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 community,
Not quite sure how best to describe it, but a table should help:
UserKey | Value | Category | Return of Formula |
1 | 20 | Blue Widgets | Red Widgets |
2 | 30 | Red Widgets | Red Widgets |
3 | 40 | Green Widgets | Green Widgets |
4 | 50 | Blue Widgets | Blue Widgets |
1 | 30 | Green Widgets | Red Widgets |
5 | 40 | Green Widgets | Green Widgets |
6 | 50 | Blue Widgets | Blue Widgets |
1 | 40 | Red Widgets | Red Widgets |
As you can see, the UserKey "1" appears 3 times, with 3 different values and 3 different categories.
I'm looking for a way to write a dax formula that checks all occurences of the UserKey, then returns the category with the highest value in the new column.
In the example, I want the new column to have "Red Widgets" because it has a value of 40 in the last row.
Hope that makes sense and someone can help me out.
Thanks,
Patrick
Solved! Go to Solution.
Hello @patrick3
Give this a try.
Return of Formula = VAR _UserKey = 'Table'[UserKey] VAR _MaxValue = CALCULATE ( MAX ( 'Table'[Value] ), ALL ( 'Table' ), 'Table'[UserKey] = _UserKey ) RETURN CALCULATE ( MAX ( 'Table'[Category] ), ALL ( 'Table' ), 'Table'[Value] = _MaxValue, 'Table'[UserKey] = _UserKey )
Hello @patrick3
Give this a try.
Return of Formula = VAR _UserKey = 'Table'[UserKey] VAR _MaxValue = CALCULATE ( MAX ( 'Table'[Value] ), ALL ( 'Table' ), 'Table'[UserKey] = _UserKey ) RETURN CALCULATE ( MAX ( 'Table'[Category] ), ALL ( 'Table' ), 'Table'[Value] = _MaxValue, 'Table'[UserKey] = _UserKey )
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |