Show associated name (string) of max value per ID (group)

[Anonymous]

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

[Greg_Deckler]

@Anonymous That's Lookup Min/Max: Lookup Min/Max - Microsoft Power BI Community

[Anonymous]

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

[Anonymous · Accepted 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]

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

[Anonymous]

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