Forum Discussion

plugwater's avatar
plugwater
Frequent Visitor
6 years ago
Solved

Choose two columns based on max value

Hi, Source table ID Category Value 10 A 10 10 B 15 20 A 20 20 B 20 Target format ID Category Value 10 B 15 20 A 20 Get the ID and Category based on ...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi plugwater ,

     

    Maybe you need to change the DAX format because of different regions.

    Measure =
VAR max_value =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) 
VAR ct =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Category], 1 ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Value] = max_value )
    ) 
RETURN
    IF (
        MAX ( 'Table'[Value] ) = max_value
            && MAX ( 'Table'[Category] ) = ct,
        1,
        0
    )

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

plugwater's avatar
plugwater
Frequent Visitor
6 years ago

Hi Liang,

Thanks.

It seems the formula has some issues - A single value for column cannot be determined

Aggregate by PL is the value/amount.

V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

Hi plugwater ,

 

Based on the sample data you provided, the pbix I created can get the expected results. You can download the pbix I provided for comparison and find out the issue.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi plugwater ,

     

    Maybe you need to change the DAX format because of different regions.

    Measure =
VAR max_value =
    CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) 
VAR ct =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Category], 1 ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Value] = max_value )
    ) 
RETURN
    IF (
        MAX ( 'Table'[Value] ) = max_value
            && MAX ( 'Table'[Category] ) = ct,
        1,
        0
    )

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • plugwater's avatar
    plugwater
    Frequent Visitor
    6 years ago

    Please provide access to download the pbix file.

    User can't be found in the qiuyunus-my.sharepoint.com directory