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

Accepted Solutions
Super Contributor

## Re: Return Name of User With Highest Number of Clients

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

Proud to be a Datanaut!

4 REPLIES 4
Super Contributor

## Re: Return Name of User With Highest Number of Clients

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

Proud to be a Datanaut!

Highlighted
Super Contributor

## Re: Return Name of User With Highest Number of Clients

@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

Frequent Visitor

## Re: Return Name of User With Highest Number of Clients

Perfect thanks! and thank you for replying

Frequent Visitor

## Re: Return Name of User With Highest Number of Clients

thanks Herbert_Liu

another great sollution, thanks for the help much appreciated!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 309 members 3,469 guests
Recent signins: