TOPN DAX Formula for Dynamic Data Filtering

TOPN DAX Function is a tool in Power BI that helps users retrieve a specified number of rows from a table (or column) based on a given criterion.

In this guide, you’ll discover the various aspects of the TOPN DAX Function, the purpose and benefits of the Power BI TOPN function, and common mistakes to avoid when using the TOPN function in Power BI.

Table of Contents:

1. Define TOPN DAX.
2. Why is DAX TOPN Important?
3. How to Use the TOPN DAX Function in Power BI?
4. How to Apply the TOPN DAX Function for Data Evaluation in Power BI?
5. What Type of Common Mistakes to Avoid When Using the TOPN Function in Power BI?
6. How Do You Efficiently Use the TOPN DAX Function in Power BI?
7. What are the Purpose and Benefits of the Power BI TOPN Function?
8. Wrap Up

First…

Define TOPN DAX.

The TOPN DAX Function in Power BI is used to rank data and retrieve a specific number of rows based on a certain condition. It’s useful for analyzing and visualizing data, and it allows users to focus on the most relevant information.

With the TOPN DAX Function, you can quickly identify top performers, anomalies, or values that meet a specific criterion within a dataset.

One of the key features of the TOPN DAX Function is its flexibility in defining the condition for ranking. Users can specify various criteria like sorting by a specific column, filtering by a certain value, or applying complex logical expressions. It allows users to customize the ranking based on their specific requirements and gain deeper insights into the data.

Here are examples of how to present your data with different visualizations:

Comparison Bar Chart

Sankey Chart

Why is DAX TOPN Important?

The TOPN function in DAX helps users extract a specified number of rows, usually the top N, from a table (or table expression) based on a defined sorting order. This comes in handy in scenarios when analysts (or business users) have to focus on a subset of data that meets certain criteria or exhibits specific characteristics.

How to Use the TOPN DAX Function in Power BI?

1. Identify Target Table: Select the table (or table expression) containing the data you want to analyze in Power BI.
2. Access Data View: Navigate to the “Data” view in Power BI to work with columns and tables.
3. Create Measure or Calculated Column: Use the TOPN function within a DAX measure (or calculated column).
4. Specify N and Sorting Criteria: Define the value of N and establish the sorting criteria to determine the top rows.
5. Apply Filter: The TOPN result can be used as a filter in visualizations to emphasize and analyze the top data points effectively.
6. Refresh Data: Refresh data to reflect any changes in the visualization based on the TOPN function.

How to Apply the TOPN DAX Function for Data Evaluation in Power BI?

Syntax

• TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

n_value

The first parameter will determine the number of rows that will be returned. A single scalar value will be returned where the expression will evaluate multiple rows.

table

This is the table from where you would like to evaluate the TOPN values.

orderBy_expression

This may consist of any DAX expression by which the result of the expression will sort and evaluate each row of the table.

order

(Optional) it will sort your expression in ascending (or descending) order.

DAX TOPN function implementation examples in Power BI

Example

EVALUATE

TOPN (

3,

ADDCOLUMNS (

“@Sales Amount”, [Sales Amount]

),

[@Sales Amount],

DESC

)

ORDER BY [@Sales Amount] DESC

EVALUATE

TOPN (

3,

ADDCOLUMNS (

VALUES ( ‘Product’[Product Name] ),

“@Sales Amount”, MROUND ( [Sales Amount], 500000 )

),

[@Sales Amount],

DESC

)

ORDER BY [@Sales Amount] DESC

— Multiple sorting criteria can be provided in further parameters

EVALUATE

TOPN (

3,

ADDCOLUMNS (

VALUES ( ‘Product’[Product Name] ),

“@Sales Amount”, MROUND ( [Sales Amount], 500000 )

),

[@Sales Amount],

DESC,

[Product Name],

ASC

)

ORDER BY [@Sales Amount] DESC

What Type of Common Mistakes to Avoid When Using the TOPN Function in Power BI?

• Incorrect Sorting Criteria: You need to ascertain that the sorting criteria specified in the TOPN function align with the desired ranking order.
• Inconsistent Data Types: Verify that data types in the sorting column match. Discrepancies lead to unexpected results.
• Ignoring Filtering Context: You need to have a good grasp of the filtering context. Ignoring it may lead to unintended outcomes, and that’s quite common when handling complex data models.
• Misunderstanding N Value: You have to be clear on the meaning of N and adjust it according to the desired number of top rows.

How Do You Efficiently Use the TOPN DAX Function in Power BI?

• Optimize Sorting: Choose the most efficient sorting column to enhance performance.
• Selective Use: Apply TOPN to relevant scenarios, and avoid unnecessary usage.
• Indexing Consideration: Assess indexing on columns used in sorting for improved query performance.
• Use in Measures: Apply TOPN within measures for customizable and dynamic analysis based on changing conditions.

Here’s a breakdown of the five stages.

Stage 1: Log into Power BI, enter your email, and click “Submit.”

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Create a Data Set and Select the Data Set to Use in the Sankey Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

• Paste the data table above into the “Power Query” window. Next, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message.

• Choose the data set you’ll want to use to create the Sankey diagram. Power BI populates the screen as shown below.

• Click on the “Create a report” dropdown. After that, select “Start from scratch.”

• The Report Canvas screen appears as shown below.

Stage 3: Add the Power BI Sankey Diagram Extension by ChartExpo

• To create the Sankey Diagram, you’ll need an add-in or the Power BI visual from AppSource. Navigate to the right side of the Power BI dashboard, and open the Power BI Visualizations panel. Next, click the ellipsis symbol (…) to import the Power BI Sankey Diagram extension by ChartExpo.

• In the next menu that opens, select the “Get more visuals” option.

• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box. You’ll see the “Sankey Diagram for Power BI by ChartExpo.”

• Click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

Stage 4: Draw a Sankey Diagram with ChartExpo’s Power BI Extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel. A window similar to the one below will open in the report section of your dashboard.

• You can resize the visual as needed. Navigate to the right side of the Power BI dashboard. You’ll see “Fields” next to “Visualizations.”

• You’ll have to select the ChartExpo visual. To select the fields, follow the sequence below:
• • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • Cost

• You’ll have to provide your email address or a ChartExpo license.

Stage 5: Activate the ChartExpo Trial or Apply a Subscription Key

• To do that, select the ChartExpo visual. You’ll see three icons below “Build Visual” in the Visualizations panel.

• Select the middle icon, “Format visual.” The visual properties will be populated as shown below.

• To begin using ChartExpo as a new user, you should enter your email address in the textbox under the “Trial Mode” section. After that, toggle “Enable Trial” to activate the 7-day trial.

• You’ll receive a welcome email from ChartExpo. The Sankey Diagram you create comes with the ChartExpo watermark.

• If you have a license key, enter the license key in the “ChartExpo License Key” textbox in the “License Settings” section. Slide the toggle switch next to “Enable License” to “On.”

• The Sankey Diagram doesn’t come with a watermark.

• To add a Prefix (like the $ sign) with the numeric values in the chart, expand the “Stats’ properties and include the Prefix value.

• To add colors to each node, expand the “Level Colors” properties and select the colors.

• Changes will be automatically saved.

Insights

• At Level 1 (Total Cost), the procurement cost is $155K.
• At Level 2 (Company Type), out of the $155K cost, $82.4K (53.3%) was spent on subcontractors, while $72.3K (46.7%) was allocated to the supplier.
• At Level 3 (Company Name), the supplier cost of $72.3K was divided into two companies: Five-star Construction and Power-up Builder, with charges of $19.3K and $53.0K, respectively.
• The subcontractor cost of $82.4K was distributed among three companies: Onyx General Contractors, Skyline Contractors, and Living Well Remodeling. They charged $30.7K, $25.6K, and $26.0K, respectively, for their services.

What are the Purpose and Benefits of the Power BI TOPN Function?

Purpose of Power BI TOPN Function:

• The TOPN function in Power BI helps in extracting a specified number of rows, usually the top N, from a table (or table expression) based on defined sorting criteria. It helps in focusing on a subset of data that meets specific conditions, and that facilitates targeted visualization and data analysis.

Benefits of Power BI TOPN Function:

• Data Emphasis: It identifies and emphasizes the most relevant (or significant) data points within a dataset. That helps users concentrate on key information.
• Top Performers Analysis: It enables the analysis of top-performing customers, products, or regions. This gives room for data-driven decision-making based on critical insights.
• Efficient Data Exploration: It enhances efficiency in exploring large datasets by narrowing down the focus to the most impactful data. This will, in turn, lead to quicker and more informed decision-making.
• Visualization Enhancement: The effectiveness of data visualizations is improved by showcasing and highlighting the outliers (or top contributors) in a dataset.

FAQs

What is Topn and Rankx in DAX?

In DAX (Data Analysis Expressions), TOPN is a function used to retrieve the top N rows based on a specified expression, while RANKX calculates the rank of each row within a table based on a given expression.

What does Topn do in DAX?

TOPN in DAX retrieves the top N rows from a table (or expression) based on a specified sorting criterion, and that helps in analyzing and presenting data with the highest values or relevance.

Wrap Up

DAX TOPN enables the extraction of the top N records using specified criteria. It’s used to identify trends, outliers, and it also helps users focus on the most relevant data points in a dataset.

When using the TOPN function in Power BI, you should avoid mistakes like overlooking aggregation, and inconsistent data types. Always be mindful of aggregations. Inappropriate aggregation may yield inaccurate results, and you also need to verify that the data types in the sorting column match. Having discrepancies will most likely lead to unexpected results.

You also need to ensure timely data refresh to reflect changes in underlying data. This prevents discrepancies between visualizations and actual datasets.

Visualization is arguably the best way to analyze data. By following the steps in this guide, you’ll become proficient in creating the Sankey Chart in Power BI.