Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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, gi...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago
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!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    The second measure gives an error message. "A table of multiple values was supplied where a single value was expected." I suspect it's because of the MAXX being passed into the filter but I'm not sure.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      10 years ago

      Not sure, here is my setup:

       

       

      MyCount formula is as described above.

       

      And, as I look closer, I think it is wrong. Grrr...

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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.