cancel
Showing results for
Did you mean:
Regular Visitor

## Return Name of User With Highest Number of Clients

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

1 ACCEPTED SOLUTION
Super User I

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 🙂

Owen Auger

My Blog
4 REPLIES 4
Microsoft

@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

Regular Visitor

thanks Herbert_Liu

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

Super User I

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 🙂

Owen Auger

My Blog
Regular Visitor

Perfect thanks! 🙂 and thank you for replying 🙂

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.