Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |