Forum Discussion

N4gash's avatar
N4gash
Frequent Visitor
3 years ago
Solved

Top N shows more rows than expected

Hi

 

I'm having troubles using Top N to show only the first 10 rows, I know that it works as expected and returns the first 10 values based on the measure. In my case it returns 18 rows.

 

My dataset is like this and i want to show only the first 10 rows:

 

 

When I apply this filter...

 

... it returns more than 10 rows:

 

 

It's ok, it's the normal behaviour of the TOP because it's based on the measure sum_winners, but I only want the first 10 names.I've tried using RANX and TOPN without success, please, anyone has the key?

 

Thanks in advance,

Regards

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi N4gash ,

    Please try below steps:
    1. below is my test table

    Table:
     

    2. create measure with below dax formula

    Sum_winners = SUM('Table'[Winners])
    Measure =
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        TOPN ( 10, ALL ( 'Table' ), [Sum_winners], DESC )
    )
VAR _name =
    SELECTEDVALUE ( 'Table'[Name] )
RETURN
    IF ( _name IN tmp, 1 )

    3. add a teable visual with fields and measure, add a measure to visual filter pane and apply

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi N4gash ,

    Please try below steps:
    1. below is my test table

    Table:
     

    2. create measure with below dax formula

    Sum_winners = SUM('Table'[Winners])
    Measure =
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        TOPN ( 10, ALL ( 'Table' ), [Sum_winners], DESC )
    )
VAR _name =
    SELECTEDVALUE ( 'Table'[Name] )
RETURN
    IF ( _name IN tmp, 1 )

    3. add a teable visual with fields and measure, add a measure to visual filter pane and apply

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    There's a tie.  Hence you see more than 10 rows.  If you want to see only 10 when there are ties, then which 10 do you want to see?