topic Re: How to display Top 10 rows in a table visual after filtering by a visual filter in DAX Commands and Tips

How to display Top 10 rows in a table visual after filtering by a visual filter

nandanu — Sun, 28 May 2023 08:26:32 GMT

Hi All,

I need to display Top 10 values in a table visual where:
(1) the rows are filtered by a visual filter with a complex measure "IncludeInTable"

(2) and then display the top 10 rows from that filtered set.

This is what I'm trying.

The problem is that it's doing the reverse. The table is first applying the Top N filter and then applying the visual filter for IncludeInTable. As a result, only those rows in the top 10 rows for which IncludeInTable evaluates to 1 are being displayed.

How do I achieve the desired result? Your help is most appreciated.

Thanks,

Nandan

Re: How to display Top 10 rows in a table visual after filtering by a visual filter

ValtteriN — Sun, 28 May 2023 14:09:15 GMT

Hi,

You can combine the top 10 logic with your filter measure. E.g.

Demo data:

Filter measure: (just an example)

Filter for top 10 = IF([Average metric]<>0,1,0)

Filtered table:

Now I will get top 3 based on ranking, but the same logic will apply for top 10.

Dax:

Filter top 3 = IF([Filter for top 10]=1 && RANKX(ALLSELECTED(Ranking),Ranking[Average metric])<=3,1,0)

End result:

Summary: instead of using pre-built top filter use RANKX function combined with your filter measure. This way the filters can be applied with one measure.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: How to display Top 10 rows in a table visual after filtering by a visual filter

nandanu — Sun, 28 May 2023 18:13:53 GMT

Hi ValtteriN ,

Thanks for your help with this. I had tried the ranking approach but the requirements are slightly different.

My filtered dataset is something like this.

How do I displayTop 10 Lost Policies in one table visual, Top 10 Bound Policies in another table, Top 10 Incomplete Policies in another etc?

Re: How to display Top 10 rows in a table visual after filtering by a visual filter

nandanu — Mon, 29 May 2023 00:41:56 GMT

ValtteriN ,

Your suggestion lead me to the solution. I modified my Ranking measure to wrap the RankX inside a calculate which runs the ranking over a filter that only includes either "Lost", or "Bound" or "Quoted" etc.

Many thanks for your help.