You do not need functions to return the top or bottom records in Microsoft Power BI. An easy filter is all that’s needed.
Image: sdx15/Adobe Stock Returning the leading or bottom records from a dataset generally requires a function or expression. Microsoft Power BI uses a filter that end users can then select to modify the visualization appropriately. The bright side is that the filter is easy to carry out. The problem is that it’s fixed. That indicates, the user only has one top or bottom choice when applying the filter.
Must-read big information coverage
In this tutorial, I’ll reveal you how to utilize Power BI’s built-in filter to show a set number of leading or bottom worths in a Power BI visualization.
I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration.pbix file, AdventureWorksSales from Github. Specifically, we will work with the Customer and Sales tables. As soon as downloaded, double-click the.pbix submit 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 learn about it (complimentary PDF) (TechRepublic)
About filters in Power BI
Power BI offers three kinds of filters:
- Power BI includes automated filters when developing a visualization. Users can customize the filters in specific ways, but they can’t be removed.
- Manual filters are readily available in the Filter pane. Users can drag them into buckets as required.
- Advanced filters are available but are more specialized.
In this tutorial, we’ll include a manual filter through the Filters pane in the Report window. We’ll utilize Power BI’s Leading filter, which utilizes the TOPN DAX tabular function to pick the leading or bottom worths from an input table based upon an expression. An input table in Power BI is another name for the source table. TOPN DAX returns a subset of the input table, however only the leading or bottom worths. This function uses the following syntax:
TOPN(
Descriptions for the arguments follow:
- n_value is the count of items you wish to return from the input table (information source table).
- table is the input table.
- orderBy_expression is the expression utilized to sort in order to return the top or bottom records.
- order defines whether the outcomes should come from top or bottom of the table. The default is DESC which implies TOP and ASC will return records from the bottom.
Prior to we can add a filter, we need a visualization.
How to construct a matrix visualization in Power BI
For presentation functions, we’ll develop an easy matrix visualization (Figure A) for two factors: They’re the easiest visualization to apply, and in this case, a matrix is legible and simple to recognize.
Figure A
Figure A: An easy matrix visualization based upon two tables, Client and Sales,
- in AdventureWorks. To develop this visualization, do the following: In the Report window, Click Matrix in the Visualizations pane. Broaden the Customer table in the Fields pane, and
- examine Customer. Broaden the Sales table in the Fields pane, and examine Sales Quantity and
Order Quantity. The result is a matrix visual that sums the variety of orders and computes the total sales for those orders by customer.
Now that we have a visualization, let’s apply a Leading filter in Power BI.
How to add a Leading filter to a visualization in Power BI
Power BI’s Filters pane shows to the right of the visualizations in the Report window. If essential, click the > icon in the upper-right corner to broaden it. Using this pane, you can set filters at 3 various levels:
- The visual level
- The page level
- The report level
The filtering pane lets you choose in between standard and sophisticated filtering (Figure B). Click the ^ icon to the right of the Consumers bucket. Doing so opens a list of basic filters. The word All denotes an unfiltered field.
Figure B
Figure B: Display the standard filters for the Consumers field. To put it simply, the visualization shows all the worths because field. If you choose among the products in the filter, which in this case is a customer, the filter will show that by displaying that consumer’s name rather of All.
Let’s include a Top filter that displays the top 10 consumers by sales amount. To do so is a fast job.
If necessary, broaden the Filters pane. The fields we wish to filter by are already in the visualization, so we don’t need to add fields. From the Filter Type dropdown, choose Leading N (Figure C).
Figure C
Figure C: Select the Top filter. Power BI shows Top in Show Products. Get in 10 in the bucket to the right. To show the bottom records, pick Bottom from this dropdown. Then, drag the Sales Amount field from the Fields pane to the By Value pail (Figure D). To see the filter at work, click Apply Filter under the Worth By container.
Figure D
Figure D: Include the Values By field, Sales Amount. As you can see in Figure E, the visualization now displays just 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 worth. To briefly arrange by the sales quantity column, click its header cell.
Figure E
Figure E: Use the filter to see the top ten customers by Sales Amount.
Stay tuned
As pointed out, completion consumer can’t choose the variety of records to see because you set that number when you include the filter. In a future article, we’ll include a slicer that lets you choose the variety of returned top or bottom records.
