Return column name with max value (for each row)

[Medmbchr]

Hi,

I have a set of 4 columns with different values, and I need to put a 5th column in which I get the name of the column with the maximum value of the 4 columns (for each row).

The thing is that I need to do it in DAX, not PowerQuery (hence no transpose or as such).

Is there any way to do it?

Thanks

[johnt75 · Accepted solution]

You could try

Col with max value =
VAR SummaryTable =
    UNION (
        ROW ( "Column name", "Column1", "Column Value", 'Table'[Column1] ),
        ROW ( "Column name", "Column2", "Column Value", 'Table'[Column2] ),
        ROW ( "Column name", "Column3", "Column Value", 'Table'[Column3] ),
        ROW ( "Column name", "Column4", "Column Value", 'Table'[Column4] )
    )
VAR Result =
    CONCATENATEX ( TOPN ( 1, SummaryTable, [Column Value] ), [Column name], ", " )
RETURN
    Result

In the event of a tie it will return a comma separated list of the column names with the max value

[RIDWIV]

seriously this is great, life saviour 👍

[Medmbchr]

Brilliant, thanks! In the event of 2 or more max values, I would like that the last column to be taken into consideration, any way to do that?

[johnt75]

you could add another column to act as the tie breaker

Col with max value =
VAR SummaryTable =
    UNION (
        ROW (
            "Column name", "Column1",
            "Column Value", 'Table'[Column1],
            "Tie breaker", 1
        ),
        ROW (
            "Column name", "Column2",
            "Column Value", 'Table'[Column2],
            "Tie breaker", 2
        ),
        ROW (
            "Column name", "Column3",
            "Column Value", 'Table'[Column3],
            "Tie breaker", 3
        ),
        ROW (
            "Column name", "Column4",
            "Column Value", 'Table'[Column4],
            "Tie breaker", 4
        )
    )
VAR Result =
    CONCATENATEX (
        TOPN ( 1, SummaryTable, [Column Value], DESC, [Tie breaker], DESC ),
        [Column name],
        ", "
    )
RETURN
    Result