The Table as its first argument applies a Row Context to whatever expression is in the
To see what’s going on in our two MEASURES, we’re going to take a quick look at some calculated COLUMNS in order to simulate the Context Transition applied by the FILTER function.
Both Option A and B are applying a FILTER on the Account table, so let’s try jumping into the Account table and create a couple of calculated COLUMNS to see the difference. The difference between Option A and Option B was how to calculate Total Orders:
Option A using DISTINCTCOUNT provides the same result for every row in the table. This should come as no surprise, we know not to use aggregate functions (such as COUNT) within a calculated column, as it doesn’t provide the correct context.
Option B using the [Total Orders] measure interestingly provides a different result for each row in the table. It does this because the MEASURE provides a Filter Context and our data model relationships allow this transition to happen.
The same thing happens within our original MEASURES; when we use the [Total Orders] measure, we get the context transition we’re looking for. When we use DISTINCTCOUNT, we get incorrect Row Context.
So, in summary, we can now update our beginner rationale for [Measure] reference inside Measures with the more complex rationale:
Note that the below calculated COLUMN in the Account table gives the correct numbers, just as using the [Total Orders] measure.
Total Orders C =
CALCULATE ( DISTINCTCOUNT ( SalesOrderDetails[Order Number] ) )
So, if we substitute the expression for Total Orders C into our original Measure, we should get the correct numbers:
Total Accounts C =
COUNTROWS (
FILTER (
Account,
CALCULATE ( DISTINCTCOUNT ( SalesOrderDetails[Order Number] ) ) > 0
)
)
And we do get the correct numbers! CALCULATE enables us to change the context of the Total Accounts measure and achieve the complex results we’re looking for.
Be the first to comment