You don’t need functions to return the top or bottom records in Microsoft Power BI. A simple filter is all that’s required.
Returning the top or bottom records from a dataset usually requires a function or expression. Microsoft Power BI uses a filter that end users can then select to modify the visualization accordingly. The good news is that the filter is easy to implement. The bad news is that it’s static. That means, the user only has one top or bottom choice when applying the filter.
In this tutorial, I’ll show you how to use Power BI’s built-in filter to display a set number of top or bottom values in a Power BI visualization.
I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWorks Sales from Github. Specifically, we will work with the Customer and Sales tables. Once downloaded, double-click the .pbix file to open it in Power BI and follow along or use a .pbix file of your own.
SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)
About filters in Power BI
Power BI offers three types of filters:
- Power BI adds automatic filters when building a visualization. Users can modify the filters in specific ways, but they can’t be removed.
- Manual filters are available in the Filter pane. Users can drag them into buckets as needed.
- Advanced filters are available but are more specialized.
In this tutorial, we’ll add a manual filter via the Filters pane in the Report window. We’ll use Power BI’s Top filter, which uses the TOPN DAX tabular function to select the top or bottom values from an input table based on an expression. An input table in Power BI is another name for the source table. TOPN DAX returns a subset of the input table, but only the top or bottom values. This function uses the following syntax:
TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])
Explanations for the arguments follow:
- n_value is the count of items you want to return from the input table (data source table).
- table is the input table.
- orderBy_expression is the expression used to sort in order to return the top or bottom records.
- order specifies whether the results should come from top or bottom of the table. The default is DESC which means TOP and ASC will return records from the bottom.
Before we can add a filter, we need a visualization.
How to build a matrix visualization in Power BI
For demonstration purposes, we’ll create a simple matrix visualization (Figure A) for two reasons: They’re the easiest visualization to apply, and in this case, a matrix is readable and easy to discern.
To create this visualization, do the following:
- In the Report window, Click Matrix in the Visualizations pane.
- Expand the Customer table in the Fields pane, and check Customer.
- Expand the Sales table in the Fields pane, and check Sales Amount and Order Quantity.
The result is a matrix visual that sums the number of orders and calculates the total sales for those orders by customer.
Now that we have a visualization, let’s apply a Top filter in Power BI.
How to add a Top filter to a visualization in Power BI
Power BI’s Filters pane displays to the right of the visualizations in the Report window. If necessary, click the > icon in the upper-right corner to expand it. Using this pane, you can set filters at three different levels:
- The visual level
- The page level
- The report level
The filtering pane lets you choose between basic and advanced filtering (Figure B). Click the ^ icon to the right of the Customers bucket. Doing so opens a list of basic filters. The word All denotes an unfiltered field.
In other words, the visualization displays all the values in that field. If you select one of the items in the filter, which in this case is a customer, the filter will reflect that by displaying that customer’s name instead of All.
Let’s add a Top filter that displays the top 10 customers by sales amount. To do so is a quick task.
If necessary, expand the Filters pane. The fields we want to filter by are already in the visualization, so we don’t need to add fields. From the Filter Type dropdown, choose Top N (Figure C).
Power BI displays Top in Show Items. Enter 10 in the bucket to the right. To display the bottom records, choose Bottom from this dropdown. Then, drag the Sales Amount field from the Fields pane to the By Value bucket (Figure D). To see the filter at work, click Apply Filter under the Value By bucket.
As you can see in Figure E, the visualization now displays only 10 records, and the values in the Sales Amount field are the top 10 sales amounts. The filter sorts them by customer and not the sales amount value. To temporarily sort by the sales amount column, click its header cell.
As mentioned, the end consumer can’t choose the number of records to see because you set that number when you add the filter. In a future article, we’ll add a slicer that lets you choose the number of returned top or bottom records.