Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AgencyPowerBi
Helper III
Helper III

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.

Capture1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

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

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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.

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 🙂

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.