Forum Discussion

andrew_hardwick's avatar
andrew_hardwick
Helper III
6 years ago
Solved

return Top xxx rows in table

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 (...
Need Help
  • ibarrau's avatar
    ibarrau
    6 years ago

    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,

  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
andrew_hardwick
Helper III
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    andrew_hardwick
    Helper III
    6 years ago

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