Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
srkase
Helper IV
Helper IV

MULTIPLE MEASURE MAX

Hi ,

 

I am new to Power BI .

 

I have created few measures like this.

 

TCr1516 = CALCULATE(SUM('PROD_CABLE'[TOTAL CR])+0,PROD_CABLE[YEAR]="20152016") // to calculate total of year 20152016
TCr1617 = CALCULATE(SUM('PROD_CABLE'[TOTAL CR])+0,PROD_CABLE[YEAR]="20162017") // to calculate total of year 20162017
TCr1718 = CALCULATE(SUM('PROD_CABLE'[TOTAL CR])+0,PROD_CABLE[YEAR]="20172018")// to calculate total of year 20172018
TCr1819 = CALCULATE(SUM('PROD_CABLE'[TOTAL CR])+0,PROD_CABLE[YEAR]="20182019") // to calculate total of year 20182019
TCr1920 = CALCULATE(SUM('PROD_CABLE'[TOTAL CR])+0,PROD_CABLE[YEAR]="20192020") // to calculate total of year 20192020
 
Is there any ways to find the max value of all this 5 measures in a single measure?
 
Regards
 
SRK
 
 
1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @srkase 

 

Try this 

Measure = 
VAR __list = { [TCr1516], [TCr1617], [TCr1718], [TCr1819], [TCr1920] }
RETURN 
MAXX( __list, [Value] )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

View solution in original post

9 REPLIES 9
EwelinaP
Frequent Visitor

I two measures, each on a separate card visual. Each card visual has separate filters applied. I want to get the max of the two measures after filters are applied. I have a third measure on it's own card visual that contains the DAX provided in this thread to show the max value between the two measures. However, when filters are selected that apply to both card visuals, the card reads 'blank', and when only filters selected that apply to one card visual, the minumim is shown. Why is this?

 

Measure = 
VAR __list = { [TCr1516], [TCr1617], [TCr1718], [TCr1819], [TCr1920] }
RETURN 
MAXX( __list, [Value] )

 

Anonymous
Not applicable

According to your description, you want to get the max/min among multiple columns. Right?

 

In this scenario, we need to unpivot the Column B, C,..., F, have them group on Column A. Then calculate the min value within Column A group. Now you can summarize Column A and the calculation into a new table and build the relationship with source table. Please refer to steps below:

 

1. Click on Column A, select "Unpivot Other Columns"

2. Then filter the empty rows.

3. Create the Min Value measure:

Min Value = CALCULATE(MIN('Table5 (2)'[Value]),ALLEXCEPT('Table5 (2)','Table5 (2)'[Column A]))

4. Create a calculated table:

Table 2 = SUMMARIZE('Table5 (2)','Table5 (2)'[Column A],"Min Value",[Min Value])

5. Build the relationship with source table source table.

Mariusz
Community Champion
Community Champion

Hi @srkase 

 

Try this 

Measure = 
VAR __list = { [TCr1516], [TCr1617], [TCr1718], [TCr1819], [TCr1920] }
RETURN 
MAXX( __list, [Value] )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 


is there a way to return the name of the var i.e

[TCr1920]

As what @Ro_  mentioned, is there a way to return the name of the measure instead of the value?

Hello, 
Thanks for this it is very useful!

Is there a way to edit this DAX to return the Name of the measure that returned the lowest value as oppose to the value its self?

Thanks,
Ross

am getting an error message boss

 

error.png

Mariusz
Community Champion
Community Champion

Hi @srkase 

 

Most likely it's your measure where you compare PROD_CABLE[YEAR]="20152016".

is PROD_CABLE[YEAR] formatted as text or number?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

previously it is as wholenumber and now changed it as text.. it works now..

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.