Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Get highest value's measure name

Hello, I have several measures tied to the IDs and I want to create a new column using DAX to bring in the name of the measure that had the highest value for each ID. 

Is this something that can be achieved with DAX with these multiple measures?? any advice is appreciated!!

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

     

     

    Best measure: =
VAR _newtable =
    SELECTCOLUMNS (
        {
            ( "Measure 01", [Measure 01:] ),
            ( "Measure 02", [Measure 02:] ),
            ( "Measure 03", [Measure 03:] ),
            ( "Measure 04", [Measure 04:] ),
            ( "Measure 05", [Measure 05:] )
        },
        "@Measurename", [Value1],
        "@Measureresult", [Value2]
    )
VAR _maxresult =
    MAXX ( _newtable, [@Measureresult] )
RETURN
    IF (
        HASONEVALUE ( 'ID'[ID] ),
        MAXX ( FILTER ( _newtable, [@Measureresult] = _maxresult ), [@Measurename] )
    )

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

     

     

    Best measure: =
VAR _newtable =
    SELECTCOLUMNS (
        {
            ( "Measure 01", [Measure 01:] ),
            ( "Measure 02", [Measure 02:] ),
            ( "Measure 03", [Measure 03:] ),
            ( "Measure 04", [Measure 04:] ),
            ( "Measure 05", [Measure 05:] )
        },
        "@Measurename", [Value1],
        "@Measureresult", [Value2]
    )
VAR _maxresult =
    MAXX ( _newtable, [@Measureresult] )
RETURN
    IF (
        HASONEVALUE ( 'ID'[ID] ),
        MAXX ( FILTER ( _newtable, [@Measureresult] = _maxresult ), [@Measurename] )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Awesome, I'll give it a try!! where does [Value1] and [Value2] come from?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Thank you for your feedback.

        I used a table constructor {  } inside the measure, and this generates random column name. [Value1] and [Value2] are those.

        If you use DATATABLE DAX function (another table constructor), you can name column names by yourself.

         

         

        DATATABLE function - DAX | Microsoft Learn