cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmauldin
Helper I
Helper I

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

 

rmauldin_2-1632251601458.png

 

 

 

 

4 REPLIES 4
Greg_Deckler
Super User
Super User

@rmauldin 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])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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
Community Support
Community Support

Hi @rmauldin ,

Basically the measure formula written by you should work.

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

vyingjl_0-1632707107381.png

 

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.

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.