Forum Discussion
Helper IHelp 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.
15 Replies
- parry2k
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.⚡
mehul26That did not work. I still end up getting duplicate counts.
- parry2k
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.⚡
- mehul26
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.
- mehul26
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
Hi
Sorry didn't understand your reply.
Can you share your file? it would be easier for us to help.
- mehul26
yes
- parry2k
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.⚡
