cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

How to show top user?

Hello,

Data:

IDUserDate
1Bob1/1/2020
2Sam1/2/2020
3Sam1/3/2020
4Bert1/4/2020
5Sally1/5/2020
6Sally1/6/2020
7Tom1/7/2020
8Bob1/8/2020
9Tom1/9/2020
10Sally1/10/2020
11Bert1/11/2020
12Billy1/12/2020
13Bob1/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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: How to show top user?

Hi,

This measure works

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Highlighted
Resolver I
Resolver I

Re: How to show top user?

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

 

Highlighted
Post Patron
Post Patron

Re: How to show top user?

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
image.png
 
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"...
image.png
...it shows "(Blank)":
image.png
 
I'm unclear on how to make the Card show, say, the Top 3 users by UserRank.
Thank you again!
Highlighted
Super User V
Super User V

Re: How to show top user?

Hi,

This measure works

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Resolver I
Resolver I

Re: How to show top user?

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

The visual below might be helpful...

Thanks

Top 3 Ranked Users.jpg

Highlighted
Resolver I
Resolver I

Re: How to show top user?

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

 

Thanks

Highlighted
Microsoft
Microsoft

Re: How to show top user?

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors