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
mehul26
Helper I
Helper I

Help with getting max record for each employee.

I have a table with Employee, Set column and Rank Colum.  For each employee I want to only bring in the max rank.  Based on my output below, my pie chart will have 3 counts for SETB and one count for SETC.  I've tried to use RANKX but no luck yet.  

 

Rank.jpg

15 REPLIES 15
parry2k
Super User
Super User

@mehul26 try this measure:

 

Max Rank = 
SUMX ( 
    SUMMARIZE ( EMp, Emp[EmpId], Emp[Set], Emp[Rank] ), 
    IF ( 
        Emp[Rank] = CALCULATE ( MAX ( Emp[Rank] ), ALLEXCEPT ( Emp, Emp[EmpId] ) ), 
        1  
    ) 
)

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@mehul26 In my very first reply I gave you the solution and that's exactly what is required. I don't know if you tested it or not, if you not, you are just unfortunately wasting time. You have to be respectful of others time and test the solution that is provided and if that doesn't work provide the feedback. I hope you will take care of it in the future.

 

Solution is attached.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 

 

I sent you a PM earlier today.  In short, your solution works for the data I gave you.  This was a mistake on my end.  I didn't realize the impact for missing columns.  If you look at the screen shot below where I added a new colum called 'TempNX'.  The measure does not work here.  For EmpliD 600001, I have two SETB's but the pie chart counts them as 2 instead of 1.  The additional columns have no coorelation with the rank of the set colum.  They are just tied to the emplid.

 

Pic.jpg

parry2k
Super User
Super User

@mehul26 you are expecting this result, correct?

 

parry2k_0-1623424047753.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

yes

aj1973
Community Champion
Community Champion

Hi @mehul26 

use this formula,

 

MAX Rank = 

var _EmplID = SELECTEDVALUE(TABLE[EMPLID])

RETURN

CALCULATE(MAX(TABLE[RANK COLUM], TABLE[EMPLID] = _EmplID)

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hi Amine,

The highlighted piece is looking for a measure and it does not like table[colum name].

 

MAX Rank = 

var _EmplID = SELECTEDVALUE(TABLE[EMPLID])

RETURN

CALCULATE(MAX(TABLE[RANK COLUM], TABLE[EMPLID] = _EmplID)

aj1973
Community Champion
Community Champion

Hi 

Sorry didn't understand your reply.

Can you share your file? it would be easier for us to help.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Rank2.jpg

I can't seem to attach my pbix file but here is an image.  Its a simple file with one table.

aj1973
Community Champion
Community Champion

 

Please use Onedrive, google drive or any cloud service to upload your file and share the link 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

See if you are able to download this pbix file.  https://mantech.box.com/s/r2apq38wtz8yc5n5c5cup6li2i38lp19

aj1973
Community Champion
Community Champion

Wasn't this you wanted to see?

 

aj1973_3-1623425069853.png

 

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

No.  Pie chart should only have 3 SETB and one SETC.

 

I have a workaound by createing a new table and pulling it in the model.  Only problem is that pie chart does not slice based on data selected in other areas.  

parry2k
Super User
Super User

@mehul26 add a measure and then use it in the pie chart:

 

Max Rank = SUMX ( Emp, IF ( Emp[Rank] = CALCULATE ( MAX ( Emp[Rank] ), ALLEXCEPT ( Emp, Emp[EmpId] ) ), 1  ) )

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

That did not work.  I still end up getting duplicate counts.  

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.