Default display 10 values after choosing one slicer

[eryn]

Hi community,

I have two slicers for my line chart, e.g. NameSpace and MetricsName. The dataset is large, so for certain NameSpace, might have hundreds of MetricsName. I am using a drop down slicer.

So it is possible when I select a NameSpace, (while not MetricsName yet), the chart shows only 10 Metrics by default, instead of all metrics in this namespace?

Noted that I must do this in Direct Query Mode.

Thanks!

[v-diye-msft]

Hi @eryn 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[SteveCampbell]

I think you are after a top N filter:

https://www.tutorialgateway.org/power-bi-top-10-filters/

[eryn]

Hi Steve,

Thanks! it's great to know this filter functionality.

But I only want this to be the by-default display, and if user want to choose more, they can go ahead. 

As I tried the Top N, the filter will locked down the number of MetricName to 10 (might be actuallty 100 tho), so user cannot choose others any more.

[SteveCampbell · Accepted solution]

One easy way, you could add a calculated column to your table:

FilterColumn = RANKX('Table','Table'[NumericColumn],,DESC,Skip)

Then add FilterColumn to a slicer and change it to "Less than or equal to" 

If you want just 10 or all, you can then make a new table (Home > Enter Data) with a two columns

NumbersColumn which just contains the numbers 1 through 10.
FilteredColumn which just contains the words "Top 10" in every row.
Add a relationship from the to NumbersColumn in the new table to FilterColumn in original table.

Then add the NumbersColumn column to a slicer as a list. You can toggle Top 10on or off

[eryn]

Thanks Steve!