MAX value best practice

dicken

Active Member
Joined
Feb 12, 2022
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hi
When trying to find the maximum value with a filter condition is there any difference between these two methods and
which if either is regarded as better;


Excel Formula:
; MAX( Table1[Units] ) 
  Then, CALCULATE([MaxU],(FILTER(ALL(Table1[Item]) ,Table1[Item]= "A") )

or

Excel Formula:
MAXX(FILTER(ALL(Table1[Item],Table1[Units]),Table1[Item] = "A"),Table1[Units] )


Richard
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi
When trying to find the maximum value with a filter condition is there any difference between these two methods and
which if either is regarded as better;


Excel Formula:
; MAX( Table1[Units] )
  Then, CALCULATE([MaxU],(FILTER(ALL(Table1[Item]) ,Table1[Item]= "A") )

or

Excel Formula:
MAXX(FILTER(ALL(Table1[Item],Table1[Units]),Table1[Item] = "A"),Table1[Units] )


Richard
CALCULATE isn't an Excel function, it's a DAX function. I assume MAXX is a typo. However unless the Item column in Table1 is only single upper case characters (A;B;C), I don't think you're going to get what you're looking for.
I suggest posting some sample data along with a sample result using XL2BB as it's unclear what the source data or goal is.
 
Upvote 0
No, this is all DAX. This is the power tools forum, after all. ;)
but power pivot, which uses DAX comes under power tools,

Excel Formula:
Power BI, Power Pivot, Power Query (Get and Transform), Power View, and Power Map

no MAXX is what I used? MAXX to take the filtered table, I just used MAX with the calculate version.
Anyway is one better than the other?

RD
 
Upvote 0
but power pivot, which uses DAX comes under power tools,

Excel Formula:
Power BI, Power Pivot, Power Query (Get and Transform), Power View, and Power Map

no MAXX is what I used? MAXX to take the filtered table, I just used MAX with the calculate version.
Anyway is one better than the other?

RD
Never mind. I didn't realize that was DAX.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top