DAX Optimization Tips for Large Models

Some thoughts in addition to MFelix:

1. Download and install DAX Studio. It is free and essential for benchmarking and profiling your DAX queries. It is basically impossible to begin any optimization without first knowing where the bottlenecks are, and DAX Studio will tell you exactly that.

2. As MFelix mentioned, things like many-to-many and bi-directional relationships are not best practice. Simplifying your Data Model will do wonders for performance, reducing potential bugs, long term maintenance, and more. This starts with implementing star schemas, but then goes further by determining the right balance between normalization and denormalization see below.

3. As a general rule, Fact table(s) should be normalized while Dimension tables should be denormalized. Each relationship that a filter has to traverse requires memory, and while it is common for relational databases to have to join through many levels (e.g. Sales[ProductID], Product[SubcategoryID], ProductSubcategory[CategoryID], ProductCategory[...], ...), doing so in DAX is not best practice.

4. Now there are exceptions to every rule, like when there is a high cardinality between your Fact and Dimension table(s) e.g. 100,000 rows. In that case, you may want to denormalize your Fact table or split your Dimension table into two.

5. Make sure your Date table includes every day. Removing days (e.g. only including the First of the Month) is  actually suboptimal.

6. Optimizing DAX and Data Models is a complex endeavor and the above points just skim the surface.