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

Thanks, I could get the values you mentioned. But in the last two rows in your screenshot, i need only one entry.

Is it possible ? 

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

Hi plugwater ,

 

Sample .pbix has been updated

Measure = 
var max_value = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[ID]))
//calculate the max value of each id
var ct = CALCULATE(FIRSTNONBLANK('Table'[Category],1),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Value]=max_value))
//any category
return IF(MAX('Table'[Value])=max_value&&MAX('Table'[Category])=ct,1,0) 
//Determine whether the current value is equal to the max value

 

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.

      • 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