Power BI Desktop, Sort By Column And DAX Calculations That Use The All() Function

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:

image

image

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:

image

…it’s possible to build a PivotTable that looks like this:

image

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:

image

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:

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

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

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]

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])))

9 thoughts on “Power BI Desktop, Sort By Column And DAX Calculations That Use The All() Function

  1. 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!

  2. 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

  3. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s