Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
jraward
New Member

Top N with duplicates clarification

I'm having a brain failure at the moment and I can't quite figure out the logic behind how BI determines the Top N from a list with some duplicates:

 

 

 

Can someone explain how there are 4, when I've asked for the top 3 and there are only 2 distinct values there; 11 & 5?

 

I assume it's because there are 3 distinct 'location' properties, but this doesn't make sense to me when I've only asked for top 3 Fruit by Qty

2 ACCEPTED SOLUTIONS
v-caliao-msft
Microsoft Employee
Microsoft Employee

Hi @jraward,

 

So for the last tow friuts, which one would like to show in your table?

 

This TOP function return the row that rank value =1, 2, 3 
Capture.PNG

Then rank value for the last two friuts=3, so both of them will be show in your table. If you need only one friut display on your table, you need to add another condition to filter it.

 

Regards,

Charlie Liao

View solution in original post

OwenAuger
Super User
Super User

Hi @jraward

Just restating what @v-caliao-msft has said:

 

A TopN Visual Level Filter in your example works by listing the values of Fruit in descending order of Qty (with tied Fruit ordered arbitrarily), then if the 3rd fruit is within a tied group, all items from that group are included (i.e. TopN cannot break ties).

 

This is the same behaviour as the TOPN DAX function.

 

You could also interpret a TopN filter on Fruit as

"Include only the Fruit that have fewer than N Fruit ranked above them."

 

The DAX for this visual filter would look something like this without using the TOPN function:

FILTER (
    ALLSELECTED ( Waller_test[Fruit] ),
    VAR QtyCurrentFruit =
        CALCULATE ( SUM ( Waller_test[Qty] ) )
    VAR FruitGreaterThanCurrent =
        COUNTROWS (
            FILTER (
                ALLSELECTED ( Waller_test[Fruit] ),
                CALCULATE ( SUM ( Waller_test[Qty] ) ) > QtyCurrentFruit
            )
        )
    RETURN
        FruitGreaterThanCurrent < 3
)

(ALLSELECTED is used since the Visual Level TopN Filter will operate in the context of any other Page/Report filters on Fruit that may exist)

 

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @jraward

Just restating what @v-caliao-msft has said:

 

A TopN Visual Level Filter in your example works by listing the values of Fruit in descending order of Qty (with tied Fruit ordered arbitrarily), then if the 3rd fruit is within a tied group, all items from that group are included (i.e. TopN cannot break ties).

 

This is the same behaviour as the TOPN DAX function.

 

You could also interpret a TopN filter on Fruit as

"Include only the Fruit that have fewer than N Fruit ranked above them."

 

The DAX for this visual filter would look something like this without using the TOPN function:

FILTER (
    ALLSELECTED ( Waller_test[Fruit] ),
    VAR QtyCurrentFruit =
        CALCULATE ( SUM ( Waller_test[Qty] ) )
    VAR FruitGreaterThanCurrent =
        COUNTROWS (
            FILTER (
                ALLSELECTED ( Waller_test[Fruit] ),
                CALCULATE ( SUM ( Waller_test[Qty] ) ) > QtyCurrentFruit
            )
        )
    RETURN
        FruitGreaterThanCurrent < 3
)

(ALLSELECTED is used since the Visual Level TopN Filter will operate in the context of any other Page/Report filters on Fruit that may exist)

 

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
v-caliao-msft
Microsoft Employee
Microsoft Employee

Hi @jraward,

 

So for the last tow friuts, which one would like to show in your table?

 

This TOP function return the row that rank value =1, 2, 3 
Capture.PNG

Then rank value for the last two friuts=3, so both of them will be show in your table. If you need only one friut display on your table, you need to add another condition to filter it.

 

Regards,

Charlie Liao

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.