Recently I came across a problem where a DAX measure gave different results in Excel and Power BI Desktop. It turned out not to be a bug or even a feature, but since it confused me for a few minutes I thought it was worth writing up in case anyone else ran into it.
Consider a model built in Excel using Power Pivot from the following two tables:
With two measures defined in the model as follows:
Sum of Sales:=SUM(Sales[Sales])
Share:=DIVIDE([Sum of Sales], CALCULATE([Sum of Sales], ALL(Month[Month Name])))
…and, importantly, the Sort By Column property on the Month Name column set to Month Number:
…it’s possible to build a PivotTable that looks like this:
However, when you import the same model into Power BI Desktop and recreate the PivotTable above in the Report view you’ll see that the Share calculation no longer gives the same values:
What’s the problem here? It’s all down to the way Power BI Desktop generates DAX queries when you set the Sort By Column property. The Excel PivotTable above generates the following MDX:
[sourcecode language=”text” padlinenumbers=”true”]
SELECT
{[Measures].[Sum of Sales],[Measures].[Share]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_VALUE,
HIERARCHY_UNIQUE_NAME ON COLUMNS ,
NON EMPTY
Hierarchize(
{DrilldownLevel({[Month].[Month Name].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_VALUE,
HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Model]
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE,
BACK_COLOR, FORE_COLOR, FONT_FLAGS
[/sourcecode]
On the rows axis, as you would expect, the only hierarchy you see is Month Name.
However, if you run a Profiler trace (you can find out how to do this here although it’s much easier to get the Process ID from DAX Studio) to look at the DAX query generated by Power BI you’ll see
[sourcecode language=”text”]
EVALUATE
TOPN (
102,
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
ROLLUPGROUP ( ‘Month'[Month Name], ‘Month'[Month Number] ),
"IsGrandTotalRowTotal"
),
"Share", ‘Sales'[Share],
"Sum_of_Sales", ‘Sales'[Sum of Sales]
),
[IsGrandTotalRowTotal], 0,
‘Month'[Month Number], 1,
‘Month'[Month Name], 1
)
ORDER BY
[IsGrandTotalRowTotal] DESC,
‘Month'[Month Number],
‘Month'[Month Name]
[/sourcecode]
The difference here is that the Month Number and Month Name fields are both present – they have to be since the query has to sort by Month Number. In MDX the order of members on a hierarchy can be set inside the model; in a DAX query you can only sort using an ORDER BY clause and for that to work, the field you’re ordering by must be present in the query.
The Share measure calculation needs to be changed in order to fix this, then. Here’s one way of doing this:
Share =
DIVIDE([Sum of Sales],
CALCULATE([Sum of Sales], ALL(Month[Month Name], ‘Month'[Month Number])))
Interesting! Thanks for sharing Chris!
Hi Chris, thanks for the tip!
I have noticed some “random” differences between Excel and Power BI, but thought it was just me. The typical scenario is that the Excel Pivot tables will correctly filter when using slicers, but when I report on the same cube using Power BI the same filter context isn’t applied. Note this is not always Sort By Column related. I have never dug into why, I have just change my DAX / schema to make it work.
Bottom line for me is that we’re not comparing apples with apples when talking about Excel Pivot Tables and Power BI DAX expressions!
More or less the same problem in the DAX Puzzle proposed by Marco Russo and Alberto Ferrari at SQLBI – https://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/
Interesting, yes it is. I should probably pay more attention to Marco and Alberto’s DAX puzzles!
Evaluate
(
Summarize
(
(
AddColumns (
(
calculatetable
(
summarize
(
‘Calendar’,
ROLLUP(‘Calendar'[Month]),
‘Calendar'[fiscalmonthintegernumber]
)
, (pathcontains(201601,’Calendar'[Year_Qtr]) || ” All”=” All”)
)
),
“CalendarMonth”, If (Isblank ([Month] ), ” All”,[Month] )
)
),
[CalendarMonth]
)
)
ORDER BY [CalendarMonth] ASC
Output:
[CalendarMonth]
All
Aug 2015
Dec 2015
Feb 2016
Jan 2016
Jul 2015
Jun 2015
Mar 2016
May 2015
Nov 2015
Oct 2015
Sep 2015
in the above dax query i am unable to add the ‘Calendar'[fiscalmonthintegernumber] in the order by clause.
Can you please help me to order the month data as I have implemented the ” All” parameters option in my SSRS report.
Please help asap.
Thanks in advance.
Regards,
Reddeppa
Chris, thank you so much for this. I have spent about ten hours banging my head against a brick wall on this very problem. Missed your article when googling – wish I had seen it earlier. I can’t pretend that I understand the MDX you’ve listed (another day perhaps…) but I do know a solution when I see one! Thanks again.
Thank You! You saved my day. I was banging my head on wall and realize that this is b/c of sorting and found your solution
Amazing !
Thanks for your help
Thank you! You saved my day!