TopN fix as 5 where the values are same

[Anonymous]

Hi All,
Here is a proble statement, on the below picture i want to show only five account based on rating and account as Asc order. where the rating of each account is same.
I use TopN as 5 but it is returning 10 account.
How do i fix the same.

[FreemanZ]

hi @Anonymous 

TOPN filter like this can't handle ties. Try to filter with a measure. 

Supposing your data looks like:

try to plot a visual table with the two columns and filter with a measure like:

Measure = 
VAR _table =
TOPN(
    5,
    ALL(TableName),
    TableName[Rating],
    ASC,
    TableName[Item],
    ASC
)
VAR _top5list = SUMMARIZE(_table, TableName[Item])
RETURN
IF(
    MAX(TableName[Item]) IN _top5list,
    1, 0
)

it works like:

[Anonymous]

Hi @FreemanZ 

Thanks for your quick response.

If we have two tables one is fact where the rating is available and we have a dimension table where items are stored.
then in that case how to modify the same measure.

[FreemanZ]

hi @Anonymous 

how are the two tables related?

[Anonymous]

Hi @FreemanZ 
These two Tables are connected to each other with one to many relationships and there are more items.

[andhiii079845]

I think if 5.0 is the highest rating and more than 5 ppl will have this rating, than PBI show all ppl with rating 5.0.

How should PBI filter this 10 ppl to get 5 ppl? This is not possible.