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.

amitchandak's avatar
amitchandak
Super User
6 years ago

plugwater ,

Try like this for category lastnonblankvalue(table[Value],min(Table[Category]))

use value as unsummarized

  • 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 ?