Filtering visuals in Power BI to the Top 10 can be achieved with slicers or Power BI Filters. Both methods give report viewers the ability to dynamically adjust the filters to the Top N records where N can be any number that they want or the Power BI developer can lock the filters down so that a report or visual will only show a maximum number of top records at any given time.
In this article, we will use Top 10 and Top N interchangeably. N represents any number that someone could choose even though Top 5 and Top 10 are by far the most common filters in reporting.
We’ll explain both the slicer and filter method, and explain why sometimes the Top N feature doesn’t appear on a visual when you’re trying to set it up.
Let’s dive in!
Table of Contents
Why it’s Useful to Limit the Records on Power BI Visualizations
One of the biggest challenges for people working in the business intelligence space is creating a dashboard that tells a concise story. Novice Power BI developers often take a kitchen sink approach and overload report users with too much information or present a cluttered dashboard.
By applying Top 10 filters to individual visuals, report pages, or entire dashboards it presents a concise view of the data that’s easier to understand. There also an added benefit of being able to present data without scroll bars when using a Matrix or Table visual without resorting to workarounds for hiding Power BI scroll bars.
Ensure Appropriate Data Types First
Before applying a Top 10 filter to a visual on a Power BI report, we recommend double checking that it’s the appropriate data type. Each column in Power BI can be defined as a number, text, or date and each one will filter differently. For example, Power BI will be unable to accurately determine the top 10 values of a column if the column is defined as text.
If you find that you setup a filter and the results make no sense, it could be a numeric field defined as text. You can tell what data type your columns currently are by looking at the Data panel of a Power BI report. Date columns are noted with calendar icons, numeric columns have a Sigma icon and text fields have no icon to the left of them.
Data type compatibility is essential when setting Top N filters. Here’s what to consider:
- Numeric Data Types: Ideal for Top N filters because they can be ordered by size or value.
- Date Types: Can be used to show the most recent or earliest dates, but are not typically used for Top N filters.
- Text Types: Require aggregation with a numeric measure for ranking purposes.
- Calculated Measures: Are often used for dynamic Top N filters and must return a numeric value.
- Aggregations: Should be numeric, such as sums or averages, to work with Top N filters.
Should you face any issues when setting up a Top N filter, ensure the data type of the field used for ranking supports numerical ordering.
Data types can be updated by right clicking on a column title in the Power Query editor, or you can update them in the Table view. Some options may not be available when working with a direct connection..
How to Setup Top N Filters in Power BI
To setup Top 10 or Top N filters in Power BI, add a visualization to your dashboard canvas. Then select it and expand the Filters Panel. In the Filters on this visual section, change the Filter Type to Top N and set Show Items to the Top 10 items you would like to see. Finally, set the By Value field to the Field that is being evaluated.
The following example shows the setup for a Matrix visualization filtering the Matrix to the Top 10 based on the Total Revenue column.
Note: One nice feature of Power BI Top N filters is that the Top field being evaluated does not actually have to show up in the visual allowing you even more flexibility in the setup. Most commonly, you would have the value shown on the visual but in the example above we purposely used different fields to highlight the capability.
Locking and Hiding Power BI Filters
After setting a Top N filter, or really any filter criteria for that matter, there is the option to lock and hide the visibility of the filter. These settings adjust how the Power BI report viewer can interact with the visual. By clicking the small Eye Icon, the filter will not be visible to the end user. By clicking the Padlock Icon, the filter will be visible to the end user but they will not be able to change it.
Make sure that you adjust these settings appropriately for your report. Some report viewers will over-use the filter panel and get themselves into some odd report views forcing them to reset the entire report, which many novice users do not know how to do.
Note: The eraser icon clears all of the filtering criteria and lets you start over or remove the filter.
Power BI Top N Filter Not an Option
When using Power BI, you might encounter a situation where the ‘Top N’ filter option isn’t available. This typically happens when you’re trying to filter on a measure. Measures are calculations that run based on the current context and return a single number (a scalar value), not a list of items that can be ranked.
To use the ‘Top N’ filter, you need to apply it to a categorical field, such as a list of products or sales regions. You can choose a measure, like “Total Profit” to determine the order of the items.
In the example below we have a matrix visual made up of a “Date” Row and “Amount” Values. The filters to the left show an “Amount” Value that allows for Top N and a “Total Revenue” Measure that only lets you filter on is less than, greater than etc.
Be careful of the type of value you are adding to the visualization. Also be aware that you can add additional fields to filter even if they’re not already added to the Values section in Power BI.
Power BI Top N Filter Not Support on All Visuals
In Power BI, most visuals allow for some form of filtering, although the methods and options can vary. There are a handful of visuals that have either limited or no built in filtering options. These include:
- Cards
- Multi-Row Cards
- Gauges
- Maps
- Some Custom Visuals
There are some workarounds such as building filters directly into custom DAX measures, but it’s hit or miss on whether they will present the data in the way that you want them to, if at all.
Power BI Top N Filter Not Working
There’s no one size fits all fast fix when a Power BI Top N Filter isn’t working, It depends on which visuals are being used and how your overall dashboard is setup. Still it’s worth considering these troubleshooting points as a starting point.
- Ensure that filters are set to evaluate as ‘AND’ if necessary, rather than acting independently.
- Use visual-level filters for individual visuals, and report-level filters to apply to the entire page.
- Select a regular category field before choosing the measure or aggregation for the Top N filter.
- Verify that the value for N is specified correctly in the filter conditions.
- Check that a measure is referenced in the Top N filter’s value field to guide the filtering process.
- Double check column and field data types.
Conclusion
In Power BI, setting up a Top 10 or Top N filter can help curate a story for report viewers and eliminate the need to scroll on certain visuals. Whether you aim to showcase your top performers or drill down into specific data segments,
Top N Filters are most commonly added using the filter panel. If the option isn’t available make sure that the visual is one that allows for the option and double check the setup, sometimes you will have to switch a Measure for a Value for Power BI to be able to appropriately evaluate it.
Other issues can arise with filtering setups not returning correct results, the first thing to check is the data type but then it will mostly depend on your specific use case.