Forum Discussion

TRK's avatar
TRK
Icon for Helper II rankHelper II
6 years ago

Filtering data by MAX value - within each year and a category - and update when other slicers used

I need to be able to identify the MAX value (of value field) by Composition for each year and filter the table to only show those MAX values.

 

I was able to do this with the following measures:

 

    Measure:

          Max Value = CALCULATE(MAX(page1_1[Value]), ALLEXCEPT(Page1_1, Page1_1[Fiscal year], Page1_1[compostion]))

 

     Calculated Column:

         IsMax =IF([Value]=[Max Value],1,0)

 

I then used the IsMax to filter the table using this column in the filter pane  and applying: If IsMax = 1

However, I am wondering how I could modify these measures so that if I deselect an item in another slicer  (ie. Blue M in the Especes slicer), it would actually recalculate the Max Value under compostion and year if there is anther species with the highest value now that the first highest is not selected.  (see 2013-14- PSP - with out Blue M would be MYE).

 

 

Thanks!

Need Help

2 Replies

    • TRK's avatar
      TRK
      Icon for Helper II rankHelper II
      6 years ago

      Unfortunately that did not give me what I need. Lookup Max returned the name of the compostion that had the highest value but what I need is the actual number (like I have in Max Value) - that works with (or like) the "isMax" (pink highlight)  so I can use that result to filter the visual to only show the max value (#)

       

      -but I want it to recalculate that if I remove one of species - ie M Blue- so that the new highest value  would be the Max Value and the ISMax would show a 1 beside the  45 (not keep the 205).  (see original post for formulas used for Max Value and IsMax)

       

      any suggestions?