Forum Discussion

Yucy's avatar
Yucy
Icon for Helper I rankHelper I
6 years ago
Solved

Need help on a DAX Formula

Thanks so much for any help. I have a table, I need to use filter dax, but never working for me   Attached is a screenshot, I need to keep the rows highlighted in yellow. Generally speaking, if num...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    This was a little trickier in DAX than I expected, but here is one way to do it.

     

    Make a Calculated Table with this expression:
    Filtered = FILTER(Ranks, Ranks[Rank]=CALCULATE(CALCULATE(MAX(Ranks[Rank]), All(Ranks), VALUES(Ranks[Number]), Ranks[Rank] <=2)))
     
    If you didn't need a separate DAX table, you could use the same Filter() expression in a Calculate() if needed.  Note that the nested Calculate()s were necessary to get context transition to kick in.
     
    If this works, please mark it as solution.  Kudos are appreciated too.
     
    Regards,
    Pat
     
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

This was a little trickier in DAX than I expected, but here is one way to do it.

 

Make a Calculated Table with this expression:
Filtered = FILTER(Ranks, Ranks[Rank]=CALCULATE(CALCULATE(MAX(Ranks[Rank]), All(Ranks), VALUES(Ranks[Number]), Ranks[Rank] <=2)))
 
If you didn't need a separate DAX table, you could use the same Filter() expression in a Calculate() if needed.  Note that the nested Calculate()s were necessary to get context transition to kick in.
 
If this works, please mark it as solution.  Kudos are appreciated too.
 
Regards,
Pat