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, May i know how to use unsummarized ?

I am really new to power BI so would appreciate more details on the formula. 

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

Hi plugwater ,

 

Create a measure and apply it to visual level filter.

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

Sample .pbix

These websites will help you learn DAX.

https://docs.microsoft.com/en-us/dax/ 

https://www.sqlbi.com/topics/dax/ 

 

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.