Forum Discussion
Measure to Count by Last Stage
You got me thinking in the right direction though. I think I have it now. Two measures:
HighestStage = CALCULATE( MAX(StageHistory[Stage]), ALLEXCEPT(StageHistory, StageHistory[Client]) ) HighestCount = CALCULATE( DISTINCTCOUNT(StateHistory[Client]), FILTER( StageHistory, [HighestStage] = StageHistory[Stage] ) )
HighestStage is just a base measure that never gets used anywhere. If you put it on the table with stages as rows you just get the highest stage repeated on every row. I suppose if I ever made a table with a client on each row it would be useful there. Anyway, HighestCount is what goes on the table, chart, whatever. Here's the result of both measures:
Stage HighestStage HighestCount 1 7 64 2 7 2 3 7 8 4 7 5 7 21 6 7 3 7 7 4
Those counts under HighestCount are correct according to my manual hand-count of the data. 64 people never made it past stage 1, everybody who reached stage 4 has moved onto a higher stage so that blank is correct.
So there's Highest. Now I need to figure out Latest. I think it'll be the same pattern though. I can use MAX on the date.
Community ChampionAnonymous - Nice, I knew I was close, but I had to run out the door to pick up my son from school. Nice job. Learning from konstantinos, you should be able to get rid of the temporary measure, I think, like this:
HighestCount = VAR HighestStage = CALCULATE( MAX(StageHistory[Stage]), ALLEXCEPT(StageHistory, StageHistory[Client]) ) RETURN CALCULATE( DISTINCTCOUNT(StateHistory[Client]), FILTER( StageHistory, HighestStage = StageHistory[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...
- Greg_DecklerThat's interesting, I'm going to have to play with this some more after my son goes to bed! Seems like a simple thing but is infuriatingly vexing.
- 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).
