Get highest value's measure name

[cherrypbi]

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!!

[Jihwan_Kim · Accepted solution]

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

[cherrypbi]

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

[cherrypbi]

I've tried this and it appears that most of IDs show correct best measure!! but some are (very few of them) totally off...where it has the wrong best measure listed.
not sure why this is happening 😞 

[Jihwan_Kim]

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

[cherrypbi]

Awesome, thanks so much for explaining. That's super helpful!

+ another question: I've tried your measure it appears that most of IDs show correct best measure!! but some are (very few of them) totally off...where it lists the wrong best measure listed.
not sure why this is happening.. any ideas?

[Jihwan_Kim]

Hi,

If it is OK with you, please share your pbix file's link (onedrive, googledrive, or other methods...), and then I can try to look into it to come up with ideas.

Thanks.

[cherrypbi]

Hello, looks like the measure worked!! thak you so much! Additionally, do you know how to create filters using that measure? 

Ex. The filter would include Measure 1, Measure 2, Measure 3, Measure 4, Measure 5.   If I select Measure 1, it will give me all the IDs that had Measure 1 as their highest value. 

I tried adding a filter but couldn't add that measure in it... thanks for looking into this! 

[Jihwan_Kim]

Hi,

I think, one of ways to achieve this is to create a new table that contains the name of each measure. And by authoring new DAX measure ( for instance, by using SELECTEDVALUES() DAX function or others), connection has to be created between the measure and the table.

I hope this helps.

Thank you.