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
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.

     

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

      Anonymous - 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]
	)
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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...