Forum Discussion

AgencyPowerBi's avatar
AgencyPowerBi
Helper III
8 years ago

Exclude duplicates from table Visual / show first largest only

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 ...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
    AgencyPowerBi
    Helper III
    8 years ago

    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 :)