Forum Discussion

Medmbchr's avatar
Medmbchr
Icon for Helper IV rankHelper IV
3 years ago
Solved

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's avatar
    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

johnt75's avatar
johnt75
Icon for Super User rankSuper User
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's avatar
Medmbchr
Icon for Helper IV rankHelper 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's avatar
    johnt75
    Icon for Super User rankSuper 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