Return Name of User With Highest Number of Clients

[j3sting]

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

[v-haibl-msft]

@j3sting

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

[j3sting]

thanks Herbert_Liu

another great sollution, thanks for the help much appreciated! 🙂

[OwenAuger · Accepted 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 🙂

[j3sting]

Hi OwenAuger 

Perfect thanks! 🙂 and thank you for replying 🙂