Choose two columns based on max value

[plugwater]

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 value, if the values are same then get any ID and Category.

Please help.

[amitchandak]

@plugwater ,

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

use value as unsummarized

[plugwater]

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]

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]

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]

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]

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]

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]

Please provide access to download the pbix file.

User can't be found in the qiuyunus-my.sharepoint.com directory

[V-lianl-msft · Accepted solution]

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.