How do you combine ALLEXCEPT and IF statements?

[snow87]

Could someone give me an example of combining allexcept and if?

[PaulDBrown]

What is it you are trying to do?

[snow87]

I have stones that have multiple stages of varying duration, with each stage possibly on a different bus line.

stone id

stage

duration

bus

I'm trying to return which bus line the stone spent the largest percentage of it's total duration (summed from all stages).

I created columns for

total stone duration

Portion of total growth duration

Highest portion per bus

and those work (with Highest Portion per Bus giving me the value for the bus in that row.)

This is the column formula for Highest Portion per bus:

Highest Portion Per Bus = CALCULATE( SUM (Growth[Portion of Total Growth Duration]), ALLEXCEPT(Growth, Growth[STONE_ID], Growth[Bus]))

I'm trying to use that to return a Bus value for each Stone ID.

I'm struggling with writing DAX for:

For each stone id, if the highest portion per bus = max(highest portion per bus), return bus

[PC2790]

With some understanding of your requirement, I suppose this will work:

FIRSTNONBLANK(TOPN(1,VALUES(Growth[Bus]),[YourMeasure/Calculatedcolumn]),1)

[snow87 · Accepted solution]

Figured out a cludgy way of doing it with multiple columns. Thanks for looking into it.

[snow87]

This gives me the same bus for all the stones. How do return the result for each stone id?