cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft

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

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

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
Blog
2 REPLIES 2
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
Blog
Microsoft

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

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors