Setting Power Pivot Slicer on Numerical Field with VBA

I built an Excel report. It loads data into data model with Power Query, visualizes with Power Pivot and provides quick navigation with Slicer. A simplified structure is as below:

Simplified structure

To automate the process, I use VBA to set slicers. In essence, Excel data model is OLAP, so slicer members can be controlled with SlicerCache.VisibleSlicerItemsList property. For example, to select January, we can write:

Sub SetSlicer()
    Dim sc As SlicerCache
    Set sc = ThisWorkbook.SlicerCaches("Slicer_month")
    sc.VisibleSlicerItemsList = Array("[Fact].[Month].&[1]")
End Sub

The element in this array uses MDX language, where "[Fact].[Month].&[1]" means the member with value 1 in column Month in table Fact. So easy, isn’t it? However —

Don’t worry. We can convert the OLAP PivotTable to Formulas to check the member expression:

PivotTable turns into CubeMember, CubeSet, CubeValue functions

It seems there should be a dot after the number. Not sure why, but let’s just add the dot:

Sub SetSlicer()
    Dim m As Integer, sc As SlicerCache
    m = 1
    Set sc = ThisWorkbook.SlicerCaches("Slicer_Month")
    sc.VisibleSlicerItemsList = Array("[Fact].[Month].&[" & m & ".]")
End Sub

It works… for nine months, until October when it broke again:

Checking with the above method again and we’ll find the member expression becomes [Fact].[Month].&[1.E1]. Alright, now it should be 1.E1, but why? Further checking November and December and we’ll find they are [Fact].[Month].&[1.1E1] and [Fact].[Month].&[1.1E1]. Obviously it’s scientific notation. The dot after numbers is a decimal point with the zero after it omitted.
Yet another problem is this isn’t the standard format, which is different from both worksheet function TEXT() and VBA function Format(). A quick fix is hardcoding the MDX string for 12 months. Although this quick, it doesn’t resolve possible other numbers in the future.

Thinking over it again, the fact that members are in scientific notation usually indicates the column is of floating-point data type. Once this is perceived, verification and resolving the issue is much easier. Just open the data model view of Power Pivot, select Month column and, lo and behold, it is decimal (decimal is floating-point):

Power Pivot column type can be integer, decimal (floating-point), etc.

Of course we can set to integer here, but I don’t feel like changing column types manually. Thinking a step further, since data are imported with Power Query, the cause should lie in data mash-up:

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type number}, {"Month", type number}, {"Sales", type number}})
in
    #"Changed Type"

That’s it. The problem is caused by setting Month column to the primitive type type number which is floating-point type. We just need to change the type of Month columns to Int64.Type, then the Month column in pivot tables as well as in slicers will become integer, and we can simply write MDX string like [Fact].[Month].&[1] in VBA. To view slicer members, besides converting pivot tables to formulas, we can also get VisibleSlicerItemsList property in SlicerCache object in VBA:

Sub ShowSlicerItems()
    Dim sc As SlicerCache
    Set sc = ThisWorkbook.SlicerCaches("Slicer_Month")
    MsgBox Join(sc.VisibleSlicerItemsList, vbCrLf)
End Sub

Note that after data type is changed, all slicers and pivot table filters will be reset, so you should set it again.

P.S.

While in VBA you must use exact MDX unique names and be cautious of data types, with CubeMember function you can relax and use either integer or floating-point MDX string without error:

I guess the Cube functions do the implicit conversion job.

P.S. 2

For numerical fields used only as dimensions like year, month and date, another common practice is setting them to text (string) type. The advantage is it won’t be summed with measures by accident. Nevertheless, the default sorting will become {1, 10, 11, 12, 2, 3, …}. The solution is adding a hidden Month column of decimal type and use it to sort the Month column of text type. Although this requires more efforts, it is an orthodox solution.