Forum Discussion

jopezzo's avatar
jopezzo
Icon for Helper I rankHelper I
7 years ago
Solved

How to filter a table based on a column's highest value

Hi,   I am trying to calculate the total quantity by Quality from a table called "Data", but filtering on the highest value in Period (in this case, 201906).  The purpose is to calculate the varian...
  • Anonymous's avatar
    Anonymous
    7 years ago
    Please use the same link as above.

    Best
    D.
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Create a DAX formula that goes something like this:

 

Measure IsHighest = 
  VAR __table = SUMMARIZE(ALLSELECTED('Table'),[Quality], [Period],"__Quantity",SUM([Quantity]))
  VAR __max = MAXX(__table,[__Quantity])
  VAR __period = MAXX(FILTER(__table,[__Quantity] = __max),[Period])
RETURN
  IF(MAX([Period]) = __period,1,0)

Then just fiilter on this being 1