Forum Discussion

srkase's avatar
srkase
Helper IV
6 years ago
Solved

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
 
 
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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


     

9 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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


     

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        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

         

    • Ro_'s avatar
      Ro_
      Regular Visitor
      3 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • EwelinaP's avatar
    EwelinaP
    Frequent Visitor
    3 years ago

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