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 ? 

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