DAX Optimization Examples

Join DAX Expert, Alberto Ferrari, as he answers the question how do you optimize a DAX expression?

In this session, we examine DAX expressions and Tabular models. Using DAX Studio and knowledge of the VertiPaq model, we explore optimization techniques.

As you will see, most optimizations are the direct application of best practices. But the session has the extra takeaway of understanding what kind of performance you should expect from your formulas. You’ll also see the improvement you might get from learning both model optimization and code optimization.

This video was recorded during the Calgary Power Platform User Group on May 13, 2020.

Watch the April Meeting.

Download the presentation and pbix file.

Key Points from Alberto Ferrari’s Presentation

The goal of the presentation is to demonstrate how to optimize a Power BI report’s performance and find the source of slow report queries.
The presentation covers how to use Performance Analyzer to measure the speed of visuals. It also covers how to use Server Timings to measure the execution time of queries.
The model used in the presentation is a star schema containing tables for orders, customers, and exchange rates.
Open orders amount in USD and amount in Euro are identified as potential issues in the report.
Measuring the relative time of each measure separately is necessary to further optimize the code.
Three measures were taken to determine the timings: amount in USD, amount in Euro, and open orders.
Open orders took the longest at around 9.5 seconds, while amount in Euro took around 2.3 seconds.
The focus should be on improving the slowest process, which is open orders.
There are two options for fixing amount in Euro: changing the measure one step at a time or finding the measure local to the query.
Finding the measure local to the query is the easier option that allows quick changes to the code.
Amount in Euro calls another measure, amount in USD, so the problem could be in either measure.
Finding the pendant measure helps to view all the measures used by another measure.
The DAX code involves computing the amount in USD for each order. This is done by iterating over orders and computing the exchange rate based on the first day of the month.
To optimize the code, the exchange rate is first stored in a variable to avoid doing the same lookup twice. The average exchange rate is also computed over all time only once outside the iteration.
The sales amount and exchange rate are then grouped by month instead of iterating over each order. This is done to reduce the number of iterations from 1.6 million to only 12.
The open orders measure was also optimized by splitting the conditions on order date and delivery date into separate parts using the calculate function.
The overall result is a much faster query. The execution time is reduced from 2.4 seconds to 33 milliseconds for the exchange rate measure. The open orders measure is reduced from 10 seconds to 74 milliseconds.
Cache can be used to optimize report performance for multiple users.
Optimization requires:

understanding the basics
learning details
downloading tools
measuring and testing
iterating multiple times

Additional Tips for Optimizing DAX

Data Analysis Expressions (DAX) is a formula language used in Power BI, Excel, and SQL Server Analysis Services. It’s an essential and powerful tool for data analysts and business intelligence professionals.

But, optimizing DAX can be challenging, especially when dealing with large datasets.

Let’s discuss some best practices for optimizing DAX to improve performance and enhance the user experience.

1. Reduce Redundancy

One of the most critical best practices for optimizing DAX is to reduce redundancy in your code. The general rule of thumb is to minimize the use of repetitive functions, codes, or calculations. It’s important to identify common calculations and create reusable code so that you don’t have to rewrite them multiple times.

2. Simplify Your Formulas

Complex formulas can be difficult to read and understand. They can also become performance bottlenecks of your report. To optimize DAX, simplify your formulas by breaking them down into smaller, more manageable pieces. Use variables and measures to simplify complex calculations. This ensures the creation of readable codes.

3. Use Aggregations

Using aggregations is an effective way to speed up DAX calculations. Aggregations allow you to pre-calculate distinct values for commonly used calculations. Doing so reduces the number of calculations required at runtime. This can significantly improve the performance of the entire report.

4. Avoid Overusing Calculated Columns

Calculated columns can be useful, but overusing them can be a performance killer. Calculated columns are calculated at the table row level, which can be resource-intensive for large data sets. Instead, use measures to perform calculations at the aggregate level.

5. Use Proper Data Modeling

Proper data modeling is essential for optimizing DAX. A well-designed, simple data model can resolve performance issues on your report. Ensure that your tables are properly related and that you have implemented the correct data types for your columns.

6. Use Indexing

Indexing is an essential tool for optimizing DAX. Creating indexes on frequently accessed columns can improve query performance. You can also use columnstore indexes to speed up the performance of complex queries.

7. Optimize Data Loading

Optimizing data loading also has a huge impact on DAX optimization. Use incremental loading to load only the data that has changed since the last load. This can significantly reduce the data load time required in your report.

8. Use the Right Visualizations

Ensure that you are using the appropriate visualizations for your data. Use simple visualizations where possible and avoid complex visuals that require multiple calculations.

Master DAX Optimization for High-Performing Power BI Reports with Iteration Insights

Optimizing DAX has a huge impact on the performance of your Power BI report. It can also enhance the user experience.

By using these best practices, you can optimize DAX and create compelling reports that are fast and efficient.

Dive deeper into the advanced features of DAX and unlock the full potential of this powerful tool. At Iteration Insights, we have a wealth of blog posts and articles that can help you expand your knowledge and enhance your Power BI skills. Don’t miss out on these valuable resources!

Learn more about DAX with Iteration Insights.

Alberto Ferrari Social Profiles

Music: https://www.bensound.com