Forum Discussion

Lucy01's avatar
Lucy01
Helper I
7 months ago
Solved

Create Measure to Return Highest Value

I’m trying to create a measure that returns the highest result out of three options.

 

For example, I want to create a trendline for student attendance and the three options are Above Average, Average, and Below Average so I would like to create a measure that returns the highest of the three. My approach is to do this one at a time, so one measure that returns Above Average, one for Average, and one for Below Average.

I’ve tried to use the below DAX but it results in an error message:

 

Comparison operations do not support in comparing values of type TRUE/FALSE with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values

 

Measure Name =

   IF(

      'Sheet 1'[Attendance Above Average] > 'Sheet 1'[Attendance Average] = "Attendance Above Average",

      'Sheet 1'[Attendance Above Average] > 'Sheet 1'[Attendance Below Average = "Attendance Above Average")

 

The results are recorded as General on the source spreadsheet as it is not a number. Do I need to convert the source spreadsheet to a number in order for the DAX to work, or is there a simpler solution to returning the highest result?

 

Thank you

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    7 months ago

    Lucy01 

    you can try this

     

    Classwork Max Result =
    CALCULATE(
    MAX('Sheet1'[Value]),
    ALLEXCEPT('Sheet1', 'Sheet1'[Month])
    )

     

    Classwork Max Category =
    VAR tbl =
    ADDCOLUMNS(
    {
    ( "Above Average", [Classwork Above Average] ),
    ( "Average", [Classwork Average] ),
    ( "Below Average", [Classwork Below Average] )
    },
    "Category", [Value]
    )
    VAR MaxVal = MAXX(tbl, [Value])
    RETURN
    SELECTCOLUMNS(
    FILTER(tbl, [Value] = MaxVal),
    "Category", [Category]
    )

     

    Are these three average data measures or columns? It's better to provide some sample data 

10 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    7 months ago

    Hi Lucy01,

    Try the below

     

    Measure Name =

       MAX ( [Attendance Above Average], MAX ( Attendance Average, Attendance Below Average ) )

     

    I am assuming [Attendance Above Average] and so on indicate a measureso I dropped the table name.

     

    Best

     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

    • Lucy01's avatar
      Lucy01
      Helper I
      7 months ago

      Hello,

       

      Thanks for your help. That very nearly works but for some reason when I put the measures into a chart, it returns the same value for each. I've checked it against my source spreadsheet and the results should definitely vary.

       

      I've tried to figure out why it's returning the same values, but I'm not sure. Any help would be gratefully received.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    7 months ago

    Hi, I am not sure how your exptected outcome looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attahced pbix file.