Forum Discussion

ian11001's avatar
ian11001
Frequent Visitor
3 years ago
Solved

How to get the Second & Third Maximum Value

Hello Good Day, I have multiple measures, I already able to get the maximum value using this command below in DAX Measure.

Max Value = 

Var list = { [Measure1],[Measure2],[Measure3],[Measure4],[Measure5]}
Return
MAXX(list,[Value])

This is the example of the values of my data
Measure1 = 5
Measure2 = 20
Measure3 = 10
Measure4 = 15
Measure5 = 20

I want to have a measure that will help me to get the second highest value, and another measure for the 3rd highest value, thank you in advance for your help!
Need Help
Show and Tell
Tips and Tricks
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    plse try this

    Max 2 Value  = 
Var list = TOPN(2,{ [Measure1],[Measure2],[Measure3],[Measure4],[Measure5]},[Value],DESC)
Return
MINX(list,[Value])

Max 3 Value  = 
Var list = TOPN(3,{ [Measure1],[Measure2],[Measure3],[Measure4],[Measure5]},[Value],DESC)
Return
MINX(list,[Value])

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    plse try this

    Max 2 Value  = 
Var list = TOPN(2,{ [Measure1],[Measure2],[Measure3],[Measure4],[Measure5]},[Value],DESC)
Return
MINX(list,[Value])

Max 3 Value  = 
Var list = TOPN(3,{ [Measure1],[Measure2],[Measure3],[Measure4],[Measure5]},[Value],DESC)
Return
MINX(list,[Value])
  • ian11001's avatar
    ian11001
    Frequent Visitor
    3 years ago

    Thank you so much for this Ahmedx , I have a follow up question regarding this, how can add a predefine text if the specific measure appear as top2 or top3 this is the one I will show on a card visualization, where can I add that statement?