return Top xxx rows in table

[andrew_hardwick]

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])

However I get the error 'The expression refers to multiple columns. Multiple column cannot be converted to a scalar value.'

Any tips?

Thanks,

Andy

[ibarrau · Accepted 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,

[ibarrau]

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,

[andrew_hardwick]

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.

[v-yiruan-msft · Accepted solution]

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

[andrew_hardwick]

Hi, yes the ALL part was the missing part, thanks!

[ibarrau · Accepted 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,