Exclude duplicates from table Visual / show first largest only

[AgencyPowerBi]

Hi,

I want to show top 10 shows each week in a table.

Unfortunatelly, some of the shows go few times a week so they show up in my visual.

Is there a way to exclude the duplicates and show only first, largest value (AMR%) for each show.

i.e.

What I want as a result is to have only one 'Vrijeme je za rukomet' on 14.january.

Also, only one 'Vijesti Nova TV' from 9.january... ect. I guess you know what I want now.

Currently I have hierarchy (description, 2nd description, Channel, Date)

 Is there a way to do it through filters/options or I have to make a separate table query and do it?

either way, please help 🙂

Tx

[v-yulgu-msft]

Hi @AgencyPowerBi,

Suppose [AMR%] is a column field in soure table, please try to create a Rank measure.

Rank =
RANKX (
    FILTER ( ALL ( 'AMR tb' ), 'AMR tb'[Date] = MAX ( 'AMR tb'[Date] ) ),
    CALCULATE ( SUM ( 'AMR tb'[AMR%] ), ALLSELECTED ( 'AMR tb'[Date] ) ),
    ,
    DESC,
    DENSE
)

Add [Rank] measure to visual level filter and set its value to 1.

If [AMR%] is a measure field, you can directly refer to it in above [Rank] measure instead of aggregating the column with SUM.

Best regards,

Yuliana Gu

[AgencyPowerBi]

Hi,

your measure produced some strange results.

Now I managed to make a workaround in a way that I

* sorted table accordig to AMR% (this is my ranking variable)

* removed the duplicates for columns (ISO week, Program1, Progrm 2, Target)

this way I'm left with programs of the same week, sorted decreasingly, with no reccuring Programs within the week

Only problem is that I cannot show only 10 rows, based on AMR%, since recurring combinations of Program 1 count as 1.

So if I have

Program 1 \ Program 2 \ Channel \ Date \ AMR%

Soccer \ England - Italy \ HTV1 \ 1.feb \ 10

Soccer \ Italy - Brazil \ HTV1 \ 2.feb \ 9

And if I choose to filter only top 1 Program 1 according to AMR%

Table visual would still show both rows.. 

Don't know why since rows are not grouped, nor the Program 1 is a hierachy.. 

So please help me circumvent this issue 🙂