cancel
Showing results for 
Search instead for 
Did you mean: 
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.






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.

 

 






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

 






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.






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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors