Return column name with max value (for each row)

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

Need Help

johnt75

3 years ago

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

Medmbchr

Helper IV

3 years ago

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

Super User

3 years ago

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

Forum Discussion

Return column name with max value (for each row)