Forum Discussion
Measure to Count by Last Stage
I thought you were right for a minute, but it didn't work after all. HighestStage is calculated per Client while HighestCount is calculated on those clients per stage, so it results in blank lines until the last stage, then it calculates correctly for just that last stage. I believe it does this for the same reason why the measure version of HighestStage returns all 7s on that table.
Effectively HighestCount is behaving like an iterator, like a weird COUNTAX. It's performing HighestStage measure on each client at each stage, then returning the client count based on the filter context that iteration returns. When you run it as a variable, it only ever gets the max stage for the whole table because the variable declaration gets calculated in the table's context, and the table has stages for rows.
All that being said, I'll bet there's a different way to do it with a variable. I wonder what would happen if I did a SUMMARIZE in the VAR statement...
Community Champion- greggyb
Resident Rockstar
None of this needs to be in measure logic based on the requirements as described.
Two calculated columns:
// DAX calculated columns LastStageFlag = FactStageTransition[Date] = CALCULATE( MAX( FactStageTransition[Date] ) ,ALLEXCEPT( FactStageTransition, FactStageTransition[ClientKey] ) ) MaxStageFlag = FactStageTransition[Stage] = CALCULATE( MAX( FactStageTransition[Stage] ) ,ALLEXCEPT( FactStageTransition, FactStageTransition[ClientKey] ) )
These indicate whether the row in the fact table represents the most recent stage for a given client, or the greatest stage for a given client.
Three measures:
// DAX measures Clients = DISTINCTCOUNT( FactStageTransition[ClientKey] ) LastStageCount = CALCULATE( [Clients] ,FactStageTransition[LastStageFlag] ) MaxStageCount = CALCULATE( [Clients] ,FactStageTransition[MaxStageFlag] )
These use the flags we defined above. Use CALCULATE() to filter on those flags, and count the clients for that subset of the table.
Easy peasy.
You could also calculate those flags in PQ, but this is an area where DAX shines over M (or I just don't know the right M yet to make that as simple and performant).
Heh, I always feel like I'm cheating when I do things in columns instead of measures. Yep, that method works. Thank you.
- greggyb
Columns: Anything that user filters / interaction with reports cannot change.
Measures: Anything that user interaction can change.
More specifically, my usual argument is that if it's known at refresh-time and unchanging in the face of filters / interaction, it should be a part of ETL, not done in the model. This sort of column logic is actually much easier to do in DAX than in Power Query, and I don't know what your ultimate source is. I'd always recommend pushing changes / transformations / new columns as far up the source chain as possible, but if this has to be done in DAX, it's not so bad.
