Forum Discussion

plugwater's avatar
plugwater
Frequent Visitor
6 years ago
Solved

Choose two columns based on max value

Hi,

Source table

IDCategoryValue
10A10
10B15
20A20
20B20

Target format

IDCategoryValue
10B15
20A20

Get the ID and Category based on value, if the values are same then get any ID and Category.

Please help.

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.

9 Replies

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