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

Need Help

2 Replies

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