Forum Discussion
Measure to Count by Last Stage
I am trying to write a pair of measures but I'm stuck. I'm working with a table that gives the history of clients through a series of processing stages. Each row represents a stage movement event, giving the stage number, the client, and the date when they were moved to that stage. There's other data as well but that's not relevant here.
The two measures I want are:
1) [LastStage] = A distinct count of clients per stage, where that stage was the most recent one for that person.
2) [HighestStage] = Same thing, but where the stage was the highest numbered stage the client was ever in regardless of date (they can be moved back to earlier stages sometimes).
So here's a simplified example data set:
Client Stage Stage Date Mike Jech 1 12/10/2015 Lou Tsegousi 1 12/10/2015 Jim Matt 3 12/11/2015 Mike Jech 2 12/12/2015 Adam Bahm 5 12/12/2015 Lou Tsegousi 2 12/13/2015 Jim Matt 4 12/14/2015 Mike Jech 3 12/15/2015 Adam Bahm 6 12/16/2015
What I'd like to be able to do is to use the list of stages as rows in a matrix or as the x axis on a column chart or something like that, and at each stage it would give the count of clients who have that as their last stage. Or highest.
I tried
[HighestStage] = CALCULATE( DISTINCTCOUNT(StageHistory[Client]), FILTER( StageHistory, MAX(StageHistory[Stage]) ) )
but since I'm using the stage as the row it's already in the filter context. The result was identical to DISTINCTCOUNT(StageHistory[Client]) except that the row for stage 0 was blank. I know I could use ALL to clear the filter context but then what? Or maybe this is the wrong method entirely. Anyone have any ideas?
11 Replies
HighestStage = MAXX(FILTER(stages,[Client]=[Client]),[Stage])
Create as a Measure.
Create another measure:
MyCount1 = COUNTX(FILTER(stages,[Stage]=[Highest]),[Stage])
Put Stage and this measure in a column chart. Make sure [Stage] is set to "Do not summarize".
Anonymous
Sorry, hit post before I was ready!!
Hmm. That just returns the max stage value for each row. And the rows are themselves stage values. So I get:
Stage HighestStage 1 1 2 2 3 3 4 4 5 5 6 6 7 7
What I'm looking for is for HighestStage to give me a count of clients per stage, but only count the clients on the stage row where that was the highest stage they reached. So a client who made it to stage 5 would not be counted on rows 1-4, but only on 5, etc.
Not sure, here is my setup:
MyCount formula is as described above.
And, as I look closer, I think it is wrong. Grrr...
