Why do DAX measures in SSAS Tabular behave differently in a Power BI Matrix and Pivot Table

[Anonymous]

There are two specific questions here. 

1) Why would a measure work differently in a Power BI matrix than in an Excel Pivot table. 

2) Why does the first measure below work, while the other two do not?

I am seeing some strange behavior using a SSAS tabular model (comparability level 1200) and the Power BI Matrix. I am tracking ordered project milestones, some of which are complete. My Power BI matrix and Excel pivot table have project name as rows and milestone as columns.

My measure calculates the maximum completed milestone order per project. This will be used in other calculations to set cell colors.

When I add the measure, I expect all the values on one row of the pivot table or matrix to have the same value. I connected to the Tabular model with Excel 2013 and my pivot table works as expected. Then, I connected Power BI to the same tabular model and added a matrix with the same rows, columns and value. In Power BI, I see a different value for each entry in the row. It is as if the matrix does not let me break out of the filter context provided by the columns of the matrix.

I found a way to make it work, but I don't understand why one measure works in both Excel and Power BI, while the other two only work in Excel. I cannot show the real data, but the images below describe the problem well.

The data looks something like this: 

Excel produces results like this: 

Power BI Matrix shows results like this: 

The first measure below works. The second two do not.

 max_complete_milestone:=
    CALCULATE(
        MAXX(
             FILTER(
        ALL(milestones[milestone_display_name], milestones[milestone_order], milestones[is_milestone], milestones[is_complete_milestone]),
                  milestones[is_complete_milestone] = 1 && milestones[is_milestone] = 1
            ),
            [max_order]
        )
    )


    /* These two versions do not work!!! */
    /*
    CALCULATE(
        MAXX(
             FILTER(
        ALL(milestones[milestone_display_name], milestones[milestone_order], milestones[is_milestone], milestones[is_complete_milestone]),
                  milestones[is_complete_milestone] = 1 && milestones[is_milestone] = 1
            ),
           MAX(milestones[milestone_order]) ------------- This does not work even though [max_order] is the exact same code.
        )
    )
    */

    /*CALCULATE(
        MAX(milestones[milestone_order]),
         FILTER(
            ALL(milestones[is_complete_milestone], milestones[is_milestone], milestones[milestone_display_name]),
            milestones[is_complete_milestone] = 1 && milestones[is_milestone] = 1
        )
    )*/

[Anonymous]

Hi there.

If you read upon context transition, iterators and how CALCULATE evaluates its filter arguments, then you'll know why you don't get what you think you should.

I'd suggest you grab a good book on DAX and read. Seriously. I'm not going to recommend a book (not to be misjudged) but you might start your learning with www.sqlbi.com.

I'll tell you a secret: Each and every measure is always, ALWAYS, wrapped up in CALCULATE. Even though you don't see it, it's always there. You say:

This does not work even though [max_order] is the exact same code.

Well, not really. When a measure is used, then CALCULATE, which is there but invisible to you, performs what's called 'context transition.' If you use only the expression that defines the measure, then you're not doing this and hence for each iteration, the result is the same. That's one of the many secrets you should learn by getting yourself a good book on DAX. If you don't do this---you can trust me---you'll see things you won't be able to explain.

Best

Darek