How to show top user?

[ericOnline]

Hello,

Data:

ID	User	Date
1	Bob	1/1/2020
2	Sam	1/2/2020
3	Sam	1/3/2020
4	Bert	1/4/2020
5	Sally	1/5/2020
6	Sally	1/6/2020
7	Tom	1/7/2020
8	Bob	1/8/2020
9	Tom	1/9/2020
10	Sally	1/10/2020
11	Bert	1/11/2020
12	Billy	1/12/2020
13	Bob	1/13/2020

Scenario:

- How would I show the top user or users in the case of tie?

Tried:

- TOPN, RANKX but can't seem to get the syntax correct.

Want:

- A tile in the report: "Top Users: Sally, 3 sessions, Bob:  3 sessions"

Any help is appreciated. Thank you!

[v-yiruan-msft]

Hi @ericOnline ，

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help anyone in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = "Top Users: " & CONCATENATEX(TOPN(1,VALUES(Data[User]),[User count]),Data[User]&", "&[User count], " sessions, ")&", sessions"

Hope this helps.

[glissando]

And this is good if you want to use a single row card...

Thanks

[glissando]

1. Add a separate table for the users (User in my example) with just the user name and join to the fact table on user.

2. Create a measure in your fact table (named Sessions in my example) to count the number of times a user is identified: e.g. 

      Session_Count = COUNT('Sessions'[User])

3. Create a measure in the User table to rank the users e.g. 

       User_Rank =
                 IF (
                      ISINSCOPE ( 'User'[User] ),
                 IF (
                     NOT ( ISBLANK ( [Session_Count] ) ),
                                RANKX ( ALL ( 'User' ), [Session_Count],, DESC, DENSE )
                )
        ) 

FYI - The user is a separate entity and should have it's own table and not be included in the fact table. Ideally it would have an ID key which would be used in the fact table to link the two together.

Let me know if this helps.

Thanks

[ericOnline]

Thank you for the expertise. Working on this now...

1. Created a new table of unique users

`UserTable = DISTINCT(SUMMARIZE('factTable',factTable[USER_NUM],factTable[USER_NAME]))`

2. Created a relationship between UserTable and FactTable on USER_NUM

2. Created a UserSessionCount measure (In a Measures table in my case. I store all Measures separate from their respective tables to keep them centralized)

`UserSessionCount = COUNT(factTable[USER_NUM])`

3. Create a UserRank measure (Again, in a dedicated Measures table as a best practice to centralize all measures)

UserRank=
 IF (
      ISINSCOPE ( 'UserTable'[USER_NUM] ),
 IF (
       NOT ( ISBLANK ( [UserSessionCount ] ) ),
            RANKX ( ALL ( 'UserTable' ), [UserSessionCount ],, DESC, DENSE )
     )

)

Results:

When adding a Card to the report, with the UserRank measure in "Fields"...

...it shows "(Blank)":

I'm unclear on how to make the Card show, say, the Top 3 users by UserRank.

Thank you again!

[glissando]

It works fine using a multi row card. Have you tried that?

The visual below might be helpful...

Thanks