Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 IDName InstructorAmount of ID's (per instructor)Max ID's
500101Arjen15
500101Ruben25
500101Wouter55
500102Jan13
500102Klaas33
500103Nick48
500103Tim88

 

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 IDName InstructorAmount of ID's (per instructor)Max ID'sName of Max
500101Arjen15Wouter
500101Ruben25Wouter
500101Wouter55Wouter
500102Jan13Klaas
500102Klaas33Klaas
500103Nick48Tim
500103Tim88Tim

 

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

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 IDName InstructorAmount of ID's (per instructor)Max ID'sName of Max
500101Arjen15Tim
500101Ruben25Tim
500101Wouter55Tim
500102Jan13Tim
500102Klaas33Tim
500103Nick48Tim
500103Tim88Tim

 

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 IDName InstructorAmount of ID's (per instructor)Max ID'sName of Max
500101Arjen15Wouter
500101Ruben25Wouter
500101Wouter55Wouter
500102Jan13Klaas
500102Klaas33Klaas
500103Nick48Tim
500103Tim88Tim

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.