Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Looking for some help please, im trying to create a measure to retrieve the name of the user with the highest number of clients, my columns are "Keyworker" (this is the user) and "carerid" (this is the client).
I have tried and failed to get this to work, it would be great if someone could point me in the right direction.
thanks
Solved! Go to Solution.
Hi @j3sting
You can use a pattern like this (pattern taken from SQLBI - Alternative use of FIRSTNONBLANK and LASTNONBLANK😞
TopUser = FIRSTNONBLANK ( TOPN ( 1, VALUES ( YourTable[Keyworker] ), CALCULATE ( DISTINCTCOUNT ( YourTable[carerid] ) ) ), 1 )
The FIRSTNONBLANK is just there to break ties.
Owen 🙂
You can also use the RANKX function to rank the client numbers and get the user name whose rank number is 1.
Assuming we have a table like below.
We can create two measures with following formulas.
Rank_Client = RANKX ( ALLSELECTED ( Table1 ), CALCULATE ( SUM ( Table1[carerid] ) ) )
Highest = CALCULATE ( ALLSELECTED ( Table1[Keyworker] ), FILTER ( ADDCOLUMNS ( VALUES ( Table1[Keyworker] ), "RankNum", [Rank_Client] ), [RankNum] = 1 ) )
Best Regards,
Herbert
Hi @j3sting
You can use a pattern like this (pattern taken from SQLBI - Alternative use of FIRSTNONBLANK and LASTNONBLANK😞
TopUser = FIRSTNONBLANK ( TOPN ( 1, VALUES ( YourTable[Keyworker] ), CALCULATE ( DISTINCTCOUNT ( YourTable[carerid] ) ) ), 1 )
The FIRSTNONBLANK is just there to break ties.
Owen 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |