Forum Discussion

andrew_hardwick's avatar
andrew_hardwick
Icon for Helper III rankHelper 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 (in this case the top 392).

 

I have a table showing 2 columns, Username and number of logins.

 

UsernameCount of login
[email protected]9374
[email protected]6192
[email protected]2981
[email protected]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
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

5 Replies

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      andrew_hardwick
      Icon for Helper III rankHelper 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