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,
I have create a below table in Power BI. I need to get max region value for each Id.
Below is the DAX I am using to cretae a table in Power BI:
I need to add one more clause which should return max region for each Id.
In case of Id 2 there are two rows, I need to get only one row with Max region value. So the output should be:
Id | Region |
1 | APAC |
2 | EMEA |
3 | NAMER |
Can anyone please help me on that?
Thanks!
Solved! Go to Solution.
Not sure how you are calculating your "max" but here is a table expression that gets your desired result from your example table. This one determines max alphabetically, but you can substitute in another expression inside the CALCULATE if that is not correct.
Main Summary =
ADDCOLUMNS (
SUMMARIZE ( Main, Main[Id] ),
"Region", CALCULATE ( MAX ( Main[Region] ) )
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat , @amitchandak ,
Thank you so much for your reply!
I tried the below expression and it works perfect.
@Rinku21 , if the basis of Max names, why Rank then?
Try like
summarize(Table, Table[id], "Region",max(Table[Region]))
Hi @mahoneypat , @amitchandak ,
Thank you so much for your reply!
I tried the below expression and it works perfect.
Not sure how you are calculating your "max" but here is a table expression that gets your desired result from your example table. This one determines max alphabetically, but you can substitute in another expression inside the CALCULATE if that is not correct.
Main Summary =
ADDCOLUMNS (
SUMMARIZE ( Main, Main[Id] ),
"Region", CALCULATE ( MAX ( Main[Region] ) )
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Try using the DISTINCT function. After SUMMARIZE, DISTINCT (Main (ID).
maybe work
Hi @marceloVVR ,
Thanks for the reply!
I need to select max region for each id. Using distinct it would be difficult to achieve the desire record.
Thanks,
Rinku
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |