SSAS Cube Calculated Members not working in Matrix with Subtotals.

[jeisenbart]

Hi All,

We are having issues with Time calculations when connecting Power BI to SSAS Multidimensional Cube in Direct Query mode.

To reproduce issue we created it on standalone machine with SQL Server 2017 Developer, latest Power BI desktop and AdventureWorks SSAS projects as example, so anyone can reproduce it.

We created simple calculated measure with pretty generic MDX to get Year Ago values based on Date Calendar hierarchy:

Internet Sales Amount YAGO =Aggregate(ParallelPeriod([Date].[Calendar].[Calendar Year] , 1,[Date].[Calendar].CurrentMember),[Internet Sales Amount]).

We then created a simple Power BI report with Matrix table:

• Filtered to Year 2013 only (year used is from Date Calendar hierarchy).
• Matrix with Rows added in specific order: Product Category, Product Line, Month Name (from [Date].[Calendar] hierarchy) and Sales Channel 
• We used calculated measure Internet Sales Amount YAGO for values.
• We enabled sub-totals on each level.
• We expanded matrix to all levels.

You can see on screen below that sub-totals for any attribute that is before Month is not showing values:

Now the same configuration in Excel works perfectly.

We know that Power BI generates DAX per Total presented on screen. 

We captured that DAX generated via Power BI per level for subtotals and for some reason it is returning NULLs for those sub-totals.

We observed this behaviour with all variations of hierarchy navigation MDX functions (ParallelPeriod, PeriodsToDate, MTD, YTD, LAG(x), LEAD(x) etc.).

Questions:

• Is this a bug?
• If it is not a bug - what is the solution/workaround (please show example based on Adventure Works) to get same results?
• Why it is fine in Excel and not in Power BI?

This affects basic MDX hierarchy navigation functions when displaying sub totals.

[v-yuezhe-msft]

@jeisenbart,

Do you get the same issue when creating Matrix visual in Power BI Desktop using existing measures of your cube?  

As per my knowledge, the issue is related to the measure you create in SSAS, I would recommend you follow the guide in the similar thread below to create your measure.

https://social.msdn.microsoft.com/Forums/windowsapps/en-US/33ffb5ea-d2dd-47bc-a38f-d45dbe57338d/gran...


Regards,
Lydia

[jeisenbart]

Hi Lydia,

If we using simple measures (like SUM, AVERAGE etc.) all is ok.

Only measures with hierarchy navigation behave like this (examples of those mentioned in original post).

As you can see in screens and description we are using Month from hierarchy in rows (Month Name) and we filter for specific year from same hierarchy (only 2013).

The hierarchy is used in both - filters (year 2013) and Rows (Month Name). It is not same case as in post presented in your solution. You also presenting solution related with Power Pivot - not Power BI. Although they using same DAX engine in the back the issue is with Matrix Visual.

You can also notice that totals are not working when you simply put Month after any attribute.

If we use Month at the begining of the matrix - is all ok. Only when we move month to next columns totals become blanks for any attribute before Month.

We described the issue based on Adventure Works sample SSAS Multidimensional database so anyone can reproduce it. Please try it yourself first and test it properly on latest versions of SQL and Power BI.

Also SCOPE is not proper solution as we would have to SCOPE any date hierarchy with any dimension combination that is used in SSAS cube and any measure that is using such calculations.

Why MDX generated for totals is not returning correct data like in Excel?

Regards,

Jacek Eisenbart

[jeisenbart]

Hi,

Do we know the status of investigation in relation to this issue?

This is critical component used by all of our clients we develop big,  enterprise Data Warehouses (hence the SSAS Multidimensional) in combination with Power BI.

Please let us know when this would be fixed or if there is any workaround as we are loosing clients trust (data not showing by just changing column order) in Power BI being mature self service BI tool (we already had to move some reports to SSRS becuse of this problem so clients can at least browse static output from SSAS Cube).

Also existing clients are moving away or holding of from Power BI for any future projects as most their requirements are Time Intelligence calculations with Sub-totals on drilldown (basic matrix).

Regards,

Jacek Eisenbart.

[jeisenbart]

Hi,

Is has been over a month now since we noticed this issue and it is still not fixed.

Please report the ivestigation output and possible fix date as soon as possible.