Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time 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
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
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 🙂
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |