Forum Discussion

snow87's avatar
snow87
Icon for Helper I rankHelper I
4 years ago
Solved

How do you combine ALLEXCEPT and IF statements?

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

Need Help
  • snow87's avatar
    snow87
    4 years ago

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

5 Replies

    • snow87's avatar
      snow87
      Icon for Helper I rankHelper I
      4 years ago

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

       

      stone idstagedurationbus

       

      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 durationPortion of total growth durationHighest 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's avatar
        PC2790
        Icon for Community Champion rankCommunity Champion
        4 years ago

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

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