Optimization Techniques for DAX Calculations in Power BI

Cardinality

Cardinality refers to the number of unique values in a column. To improve performance, it's best to maintain columns with low cardinality. DAX stores each unique value in the column only once. High cardinality columns imply more memory usage and increase calculation time.

Relationships

Managing relationships between tables impacts query performance in Power BI. Use relationships wisely, and ensure they are not unnecessarily complex. A simple, well-structured model will perform better than a complex one.

1. Try to avoid bidirectional relationships as they can often be limiting and have a performance impact due to the row-level security.
2. Use active relationships over inactive ones because DAX has to resolve inactive relationships and this can slow down your performance.

Column Storage Optimization

Use "Columnstore Index" to improve the performance of most of your queries. It's a technology for storing, retrieving, and managing data by using a columnar data format; it helps significantly improve query performance as it reduces the amount of I/O and CPU.

Sample DAX code to Minimize Cardinality

In this example, assume that you have a column called 'Full Name' with high cardinality since each row contains a unique value. You can create two new columns 'First Name' and 'Last Name' to lower the cardinality.

First Name = LEFT('Table'[Full Name], SEARCH(" ", 'Table'[Full Name]) - 1)

Last Name = RIGHT('Table'[Full Name], LEN('Table'[Full Name]) - SEARCH(" ", 'Table'[Full Name]))

Remember that every single action that you can take to reduce the amount of memory that your data model needs to reserve and to manage, supports the performance of all your DAX calculations.

In order to learn more about these optimizations and data modelling in general, I would advise taking some courses from the Enterprise DNA platform. It goes deep into these topics and provides practical projects for you to exercise your knowledge.