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 experts!
I'm in need of some help. I have a table where I show the registration ID's (not unique), name of the instructor, the amount of ID's per instructor and a column where I show the MAX amount of ID's per instructor. Per row I can see the max amount of corresponding ID's of the instructor.
How I created the Max ID's column:
Max ID's =
CALCULATE(MAX(Activity[Amount of ID's (per instructor)]),
ALLEXCEPT(Activity,Activity[Registration ID]))
This is what I have now:
Registration ID | Name Instructor | Amount of ID's (per instructor) | Max ID's |
500101 | Arjen | 1 | 5 |
500101 | Ruben | 2 | 5 |
500101 | Wouter | 5 | 5 |
500102 | Jan | 1 | 3 |
500102 | Klaas | 3 | 3 |
500103 | Nick | 4 | 8 |
500103 | Tim | 8 | 8 |
Now my question is: How do I get the associated name of the max value in each row per ID. The 'Name of Max' needs to be in a new column. Like in the following example:
Registration ID | Name Instructor | Amount of ID's (per instructor) | Max ID's | Name of Max |
500101 | Arjen | 1 | 5 | Wouter |
500101 | Ruben | 2 | 5 | Wouter |
500101 | Wouter | 5 | 5 | Wouter |
500102 | Jan | 1 | 3 | Klaas |
500102 | Klaas | 3 | 3 | Klaas |
500103 | Nick | 4 | 8 | Tim |
500103 | Tim | 8 | 8 | Tim |
If you want the pbix file, you can download it from my OneDrive: https://hrnl-my.sharepoint.com/:u:/g/personal/0972624_hr_nl/EZPmTmUGRfVMq9k1huLCKeMBnoYF8gL8sUoufoqj...
Help is appreciated a lot!
Thanks!
- Arjen
Solved! Go to Solution.
Hi @Anonymous,
You can try to use the following measure formula to get the name of max id and group by current id:
Name of Max ID =
VAR currID =
SELECTEDVALUE ( Activity[Registration ID] )
VAR maxAID =
CALCULATE (
MAX ( Activity[Amount of ID's (per instructor)] ),
ALLSELECTED ( Activity ),
VALUES ( Activity[Registration ID] )
)
RETURN
LOOKUPVALUE (
Activity[Name Instructor],
Activity[Registration ID], currID,
Activity[Amount of ID's (per instructor)], maxAID
)
Regards,
Xiaoxin Sheng
@Anonymous That's Lookup Min/Max: Lookup Min/Max - Microsoft Power BI Community
Thanks Greg Deckler,
That's indeed what I need and tried. There is one problem. I get the max of all ID's, like shown underneath:
Registration ID | Name Instructor | Amount of ID's (per instructor) | Max ID's | Name of Max |
500101 | Arjen | 1 | 5 | Tim |
500101 | Ruben | 2 | 5 | Tim |
500101 | Wouter | 5 | 5 | Tim |
500102 | Jan | 1 | 3 | Tim |
500102 | Klaas | 3 | 3 | Tim |
500103 | Nick | 4 | 8 | Tim |
500103 | Tim | 8 | 8 | Tim |
I need to get the name per registration ID. So for 500101 I need to see Wouter as solution in the first three rows. See next example:
Registration ID | Name Instructor | Amount of ID's (per instructor) | Max ID's | Name of Max |
500101 | Arjen | 1 | 5 | Wouter |
500101 | Ruben | 2 | 5 | Wouter |
500101 | Wouter | 5 | 5 | Wouter |
500102 | Jan | 1 | 3 | Klaas |
500102 | Klaas | 3 | 3 | Klaas |
500103 | Nick | 4 | 8 | Tim |
500103 | Tim | 8 | 8 | Tim |
How do I categorize that?
Much appreciated!
- Arjen
Hi @Anonymous,
You can try to use the following measure formula to get the name of max id and group by current id:
Name of Max ID =
VAR currID =
SELECTEDVALUE ( Activity[Registration ID] )
VAR maxAID =
CALCULATE (
MAX ( Activity[Amount of ID's (per instructor)] ),
ALLSELECTED ( Activity ),
VALUES ( Activity[Registration ID] )
)
RETURN
LOOKUPVALUE (
Activity[Name Instructor],
Activity[Registration ID], currID,
Activity[Amount of ID's (per instructor)], maxAID
)
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
Thanks a lot for your solution! This is indeed what I needed.
Do you have any idea if this is also possible in a new calculated column?
Regards,
- Arjen
Hi @Anonymous,
Ok, you can take a look at the calculated column formula version:
Name of Max ID =
VAR currID = Activity[Registration ID]
VAR maxAID =
CALCULATE (
MAX ( Activity[Amount of ID's (per instructor)] ),
FILTER (
ALL ( Activity ),
[Registration ID] = EARLIER ( Activity[Registration ID] )
)
)
RETURN
LOOKUPVALUE (
Activity[Name Instructor],
Activity[Registration ID], currID,
Activity[Amount of ID's (per instructor)], maxAID
)
Notice:
1. calculated column expression does not respond with filter effects, if you want to get the filtered result dynamically, please use the measure version instead.
2. data level of power bi:
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)
Regards,
Xiaoxin Sheng
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |