Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table showing users that have logged into a system. The total table has around 550 users (this can vary), and from this table I need to be able to show the ones who have logged in the most (in this case the top 392).
I have a table showing 2 columns, Username and number of logins.
Username | Count of login |
abc@domain.com | 9374 |
def@domain.com | 6192 |
ghi@domain.com | 2981 |
xyz@domain.com | 2510 |
Ideally I would like to add an extra column to this table, showing if they are a Top user or not. So the 392 with the highest number of logins will be flagged as a top user, the other remaining users (variable, but around 160), will not be.
I have tried using the formula:
Measure 2 = TOPN(392,staff,Staff[Login History.LoginTime])
Solved! Go to Solution.
Hey, try ALL(Staff[Username]) for the table instead of just staff. You need to calculate the rank of one row against all the rows in order to check the ranking. That's why it's 1 for you.
Hope this helps,
Happy to help!
Hi @andrew_hardwick ,
Please update the formula of your measure as below:
Rank =
RANKX (
ALL ( Staff[Username] ),
CALCULATE ( COUNT ( Staff[Login History.LoginTime] ) ),
,
DESC,
DENSE
)
Best Regards
Rena
Hi there. The function you are looking for is RANKX. The TOPN returns a table and you need a scalar showing the ranking. Try that function like: RANKX(Table, [Measure]).
Regards,
Happy to help!
Hi @ibarrau . I tried that, but rank shows as 1 each time.
The formula I used was:
Rank = RANKX(Staff,count(Staff[Login History.LoginTime]))
I used Count as without this, I got an error saying:
A single value for column 'Login History.LoginTime' in table 'Staff' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Hi @andrew_hardwick ,
Please update the formula of your measure as below:
Rank =
RANKX (
ALL ( Staff[Username] ),
CALCULATE ( COUNT ( Staff[Login History.LoginTime] ) ),
,
DESC,
DENSE
)
Best Regards
Rena
Hi, yes the ALL part was the missing part, thanks!
Hey, try ALL(Staff[Username]) for the table instead of just staff. You need to calculate the rank of one row against all the rows in order to check the ranking. That's why it's 1 for you.
Hope this helps,
Happy to help!
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |