Mastering DAX in Power BI: Why Best Practices and Optimization are Crucial

In this article we'll explore what DAX is, why it's important, and how we can make the most of this tool in Power BI.

What is DAX and why does it matter?

DAX is a formula language that allows users to create expressions for complex calculations and analysis in Power BI. It offers a wide range of functions and operators that enable the creation of measures and calculated columns, as well as effective data manipulation. With DAX, users can perform sophisticated calculations and create dynamic visualizations that aid in understanding and making informed decisions.

Mastering DAX is essential for creating effective reports and dashboards in Power BI. With proper knowledge of its capabilities, users can analyze data more accurately and extract valuable insights to drive business growth.

Best Practices in DAX

Let’s take a closer look at some of the recommended practices:

• Simplicity and Clarity: It's crucial to keep formulas simple and easy to understand.  For example, using comments in the code and using variables in measures. This not only facilitates report maintenance and sharing but also improves the readability of formulas for other users.

• Use of Descriptive Naming: Using descriptive names for measures and calculated columns makes it easier to understand the logic behind each calculation, especially when dealing with complex data sets.

• Avoiding Redundant Calculations: Avoid unnecessary repetitive calculations by reusing measures and use variables whenever possible. This not only improves performance but also simplifies model maintenance.

• Formula Optimization: Seek to optimize formulas to ensure calculations run efficiently. This includes minimizing the use of functions that may negatively impact performance, such as functions that require significant computational resources. This type of analysis is best served by two tools: Performance Analyzer in Power BI and external tool DAX Studio. 

• Relationship Management and Data Modeling: It is important to ensure proper relationships between tables and perform correct data modeling to ensure accurate and consistent results, trying whenever possible to use the star schema.

Star schema refers to a data modeling approach that arranges tables into Facts (quantitative data) and Dimensions (descriptive data). Dimensions might store data like products, stores, employees, customers, etc, while Facts might store data like sales history, transactions, inventory quantities. Dimension tables link to Fact tables.

It is considered a best practice when implementing a star schema that the cardinality should be 1 (Dimension table) to many (Fact table) (i.e. one Dimension record to many Fact records), and the cross filter direction should be from the Dimension to the Fact table. The following shows a sample model with a Sales Fact table and Date, Product, Customer, and Stores Dimensions tables.

‍

Following these practices helps create more robust data models that are easy to maintain and provide better performance when executing queries and updates in Power BI.  Now that we’ve covered best practices let’s look more closely at DAX optimization.

DAX Optimization for Better Performance.

In addition to following best practices, DAX optimization also plays a key role in improving Power BI performance. Some tips for optimizing DAX and enhancing Power BI efficiency include:

• Reduce the Number of Rows and Columns in Use: Avoid including unnecessary rows and columns in datasets, which can overload the system and slow down performance.

• Utilize Cache Storage: Take advantage of cache storage to temporarily store the results of frequent queries, which can speed up the loading time of reports and dashboards.

• Monitor and Fine-Tune Performance: Regularly monitor Power BI performance and adjust formulas as needed, ensuring the system runs efficiently.

Implementing these optimization practices can significantly improve Power BI performance and ensure a smoother and more effective data analysis experience.

Now that we’ve covered some general best practices in DAX, let’s focus on some specifics and look into CALCULATE and KEEPFILTERS.

Best Practices Applied: CALCULATE and KEEPFILTERS 

CALCULATE and KEEPFILTERS are two frequently used and important functions in Power BI.  In this section we’ll discuss some tips and best practices for their use. But first let’s explain two very important concepts to help you understand the examples:

Row Context: This is the context in which DAX formulas are evaluated, row by row in a table. When you are creating a formula in a calculated column or measure, Power BI evaluates each row individually, considering the values present in each row. This is useful when you need to perform calculations based on specific values in each row.

Filter Context: This is the context in which DAX formulas are evaluated based on the filters applied to the data. The filter can be applied through a slicer, a specific selection in a chart, or another form of filtering. The filter context affects the calculation of a measure or calculated column by limiting the data to which the formula applies.

Understanding the difference between these two contexts is important when creating complex measures or using more advanced DAX functions in Power BI. Understanding how formulas are evaluated in different contexts can help avoid unexpected or incorrect results.

In Power BI, the CALCULATE function plays a pivotal role in modifying filter contexts and altering calculations, thereby enabling more sophisticated data analyses. However, its utilization demands a comprehensive understanding of its intricacies and the precautions necessary to ensure accurate and reliable results. This article delves into the significance of the CALCULATE function, especially in the context of variables, and explores the advantages of employing the KEEPFILTERS function over FILTER.

The Importance of the CALCULATE Function

The CALCULATE function in Power BI is indispensable for manipulating filter contexts and creating dynamic calculations.

It allows users to override existing filters, apply new filters, and modify the context in which a formula is evaluated. By utilizing the CALCULATE function effectively, users can perform complex calculations and create insightful visualizations that aid in decision-making processes.

Precautions with the CALCULATE Function, Especially in Variables

When utilizing the CALCULATE function, it is essential to exercise caution, particularly when working with variables. Here are some precautions to consider:

• Understanding Context Transition: Be mindful of how the CALCULATE function interacts with row and filter contexts, as it can significantly impact the results of calculations.

• Avoiding Incorrect Results: Ensure a thorough understanding of the data model and the relationships between tables to prevent unexpected or incorrect calculation outcomes.

• Managing Filter Interactions: Properly manage and manipulate filters to maintain the integrity and accuracy of the data analysis, especially when dealing with complex data sets.

Adhering to these precautions helps ensure the proper and effective utilization of the CALCULATE function, reducing the likelihood of errors and discrepancies in the analytical outputs.

Examples of why you should be careful when using CALCULATE with Variables

Let’s compare two measures to calculate total sales for a specific product:

Measure 1:

‍

Total Bike Sales 1 =

VAR TotalSales =

    SUMX (

        'VF Cycles Sales',

        'VF Cycles Sales'[Quantity_Sold] * RELATED ( 'VF Cycles Products'[Unit Price] )

    )

VAR BikeSales =

    CALCULATE (

        TotalSales,

        KEEPFILTERS ( 'VF Cycles Products'[Product Category] = "Bikes" )

    )

RETURN

    BikeSales

Measure 2:

‍Total Bike Sales 2 =

VAR BikeSales =

    CALCULATE (

        VAR TotalSales =

            SUMX (

                'VF Cycles Sales',

                'VF Cycles Sales'[Quantity_Sold] * RELATED ( 'VF Cycles Products'[Unit Price] )

            )

        RETURN

            TotalSales,

        KEEPFILTERS ( 'VF Cycles Products'[Product Category] = "Bikes" )

    )

RETURN

    BikeSales

On the surface, there’s no difference here: both measures use variables to store total sales, and then both use that value to generate the total sales for bikes.

However, these two approaches can lead to different results depending on the placement of the variables in each measure. Let’s look at the output:

The above measures seemed to use the same approach, but have very different results.

To understand why, we need to understand the way variables work. In DAX, when a variable is declared, it is “fixed” within the scope of the measure .That is, when we first declare our TotalSales variable, which corresponds to the sales associated with all products, Power BI calculates the value once, and then never again.

The second variable then introduces a CALCULATE function to determine bike sales, but Power BI has already set a value for the TotalSales variable. It won’t be recalculated, so the product category filter does nothing.

In our second measure, Total Bike Sales 2, we take a slightly different approach by starting with the BikeSales variable.

We don’t declare our TotalSales measure until we introduce the CALCULATE function. This effectively changes the scope for the variable. Because CALCULATE applies filters first before it performs an aggregation, by the time the TotalSales variable is introduced, we’ve already filtered our sales and product tables based on category, so we only get bike sales in the result. 

By comparing these two examples, we can see the importance of filter context in relation to the CALCULATE function, and how that filter context can consequently influence the final result of the measure.

It is important to understand how the filter context and row context are affected when using variables in conjunction with the CALCULATE function to avoid unexpected or incorrect results in your Power BI reports.

The Merits of Using KEEPFILTERS over FILTER

While both the KEEPFILTERS and FILTER functions are integral to Power BI, the former offers distinct advantages over the latter in certain scenarios. Here's why using KEEPFILTERS is often preferred:

• Preservation of Existing Filters: KEEPFILTERS preserves the existing filters in a context, allowing users to apply additional filters without overriding the original ones. This ensures the accurate evaluation of calculations without disregarding essential data points.

• Prevention of Context Alterations: Unlike FILTER, which can alter the filter context, KEEPFILTERS maintains the original context, thus preventing unexpected changes to the data set's filtering behavior during the calculation process.

• By leveraging the KEEPFILTERS function, users can maintain data integrity, ensure precise calculations, and create a more reliable and robust analytical framework within Power BI.

• While not immediately obvious, and often rather small, KEEPFILTERS requires a shorter query plan for the DAX engine to retrieve results as compared to the FILTER function, which in turn can provide a very slight boost to performance. The reason behind this is that KEEPFILTERS works with individual columns, while the FILTER function is a table-constructor and iterator, and requires a bit more work on the part of the query engine to process.

Mastering the intricacies of the CALCULATE function and understanding the significance of using KEEPFILTERS over FILTER are essential for harnessing the full potential of Power BI. By exercising caution and adhering to best practices when employing the CALCULATE function, especially in variables (as we saw above), users can ensure accurate and reliable data analysis results. Similarly, by recognizing the benefits of utilizing the KEEPFILTERS function, users can maintain the integrity of the original context and enhance the precision of their calculations within Power BI.

Example of preferring KEEPFILTERS over FILTER (or no explicit filter function at all)
Measure 1:

KEEPFILTERS Bikes =

CALCULATE (

    [Total Revenue],

    KEEPFILTERS ( 'VF Cycles Product Categories'[Product Category] = "Bikes" )

)

Measure 2:

FILTER Bikes =

CALCULATE (

    [Total Revenue],

    FILTER (

        ALL ( 'VF Cycles Product Categories' ),

        'VF Cycles Product Categories'[Product Category] = "Bikes"

    )

)

‍Measure 3:

NO FILTER Bikes =

CALCULATE (

    [Total Revenue],

    'VF Cycles Product Categories'[Product Category] = "Bikes"

)

In the first example, the "KEEPFILTERS Bikes" measure uses the KEEPFILTERS function to preserve the external filters applied to the 'Sales' table, while in the second example, the "FILTER Bikes" measure uses the FILTER function to first remove filter context with the ALL function (a common practice), to filter the product table on the Bike category.

Our third measure, “NO FILTER Bikes", doesn’t use an explicit filter function at all, and simply applies a filter directly to the category (a very common practice). Let’s take a look at the results:

‍

‍

What exactly happened here, particularly in our “NO FILTER Bikes” measure? Because we used ALL in the “FILTER Bikes” measure, perhaps we should expect this outcome. The FILTER function in this case, through the use of the ALL function, first removed all external filters, supplied by the product category column (as a row context). As a result, the sales for bikes are calculated on each row in the table since the categories are ignored, which may nonetheless be a bit surprising until we understand that we’re removing, not retaining outside filters when we use ALL. 

The “NO FILTER Bikes” measure output, on the other hand, may be a bit more unexpected.

On the surface, it appears to be closer to the KEEPFILTERS measure than to the FILTER version. Again, what we need to understand is that KEEPFILTERS is sensitive to external contexts, so we have no sales for non-bike categories. Our NO FILTERS measure, on the other hand, as much as it seems like the KEEPFILTERS version, is actually using a bit of short-hand (also known as “syntax sugar”). Under the hood, it is identical to the “FILTER Bikes” measure, so we’re unwittingly stripping away external filter contexts - the ALL is implied!

As a consequence, it should be clear that KEEPFILTERS is a far better option than either of the other two methods since it respects external filter conditions. Even if we grant that we’d rarely build a table like the above example, seeing those results should be enough to convince us that using KEEPFILTERS over FILTER (or no explicit filter function) inside CALCULATE is a far better habit.

Conclusion

Mastering DAX and implementing best practices and optimization in Power BI is essential for creating robust and accurate data analyses. By following best practices and optimizing formulas, users can ensure efficient performance and a deeper understanding of data, enabling informed and strategic decision-making.

How can The Virtual Forge help?

If you’re seeking expert help with unlocking and understanding a specific aspect of Power BI, our business intelligence consultants are ready to show you how.

Our professionals can provide expert assistance in various services, such as planning analytics consulting, integration, implementation, dashboard design, QA, and training.

Feel free to get in touch with us.