Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Try like this for category lastnonblankvalue(table[Value],min(Table[Category]))
use value as unsummarized
Thanks, May i know how to use unsummarized ?
I am really new to power BI so would appreciate more details on the formula.
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
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.
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 ?
Hi @plugwater ,
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.
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.
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.
Please provide access to download the pbix file.
User can't be found in the qiuyunus-my.sharepoint.com directory
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |