Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Max value with multiple filters

I am needing to calcualte the Max Value for a data set that has multiple categories to sort through. The Max value needs to be the Max value of each ProductRef within each SubCatName. My current calcuated column is only giving me the Max value based on the ProdectRef and ignoring the SubCatName. 

 

Current Measure:   

MaxValue = VAR maxvalue = CALCULATE(MAX(robintable[ConvertedPrice]), ALLEXCEPT(robintable, robintable[SubCatName], robintable[ProductRef])) return maxvalue

 

 

 

 

 

General Comment
Need Help
Tips and Tricks

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Try:

    New Measure:   

MaxValue =  
  VAR __SubCatName = MAX('robintable'[SubCatName])
  VAR __ProductRef = MAX('robintable'[ProductRef])
RETURN
  MAXX(FILTER(ALLSELECTED('robintable'),[SubCatName]=__SubCatName && [ProductRef] = __ProductRef),[ConvertedPrice])


or if you don't care about the productref:

MaxValue =  
  VAR __SubCatName = MAX('robintable'[SubCatName])
RETURN
  MAXX(FILTER(ALLSELECTED('robintable'),[ProductRef] = __ProductRef),[ConvertedPrice])
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tried both of those options, and neither worked. Have any other ideas? I need it to look at each SubCatName and then the ProductRef with in that SubCatName and give me the Max value for all dates

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      My original dax of 

      MaxValue = VAR maxvalue = CALCULATE(MAX(robintable[ConvertedPrice]), ALLEXCEPT(robintable, robintable[SubCatName], robintable[ProductRef])) return maxvalue

      is giving me the max value of each SubCatName and not the max value of the ProdcutRef within each SusCatName

      • v-yingjl's avatar
        v-yingjl
        Community Support
        4 years ago

        Hi Anonymous ，

        Basically the measure formula written by you should work.

        Measure = 
CALCULATE(
    MAX('robintable'[ConvertedPrice]),
    ALLEXCEPT(
        robintable,
        'robintable'[SubCatName],
        robintable[ProductRef]
    )
)

         

        Perhaps you can check whether you have applied any other visual filters or page filters in the report.

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.