DAX Optimization Tips for Large Models

[Anonymous]

Greetings community.
I’m currently working on optimizing a large Power BI report with several DAX measures, and I’m looking for advice on improving performance. The dataset contains millions of rows, and some of the visuals are taking too long to render, especially when multiple measures are added to the same visual. And Power BI report where users can filter data based on different time periods like Year, Month, Week, and Day. I’ve created DAX measures to handle these dynamic time selections, but I’m noticing performance issues, especially when switching between different time granularity.

Here is the DAX formula for one measure: based on the toggle selection (these are individual 3 measures for challenge)

At End Elig - Total Completions = CALCULATE(DISTINCTCOUNT(PBI_CHALLENGES[Challenge Participants]), FILTER(PBI_CHALLENGES,PBI_CHALLENGES[CHALLENGE_COMPLETE_FLG]=1), FILTER(PBI_CHALLENGES,PBI_CHALLENGES[CHALLENGE_JOIN_DATE] >= [MinDate] && PBI_CHALLENGES[CHALLENGE_JOIN_DATE] <= [MaxDate]), FILTER(PBI_ELIGMEMBER,PBI_ELIGMEMBER[BIEFFECTIVEDATE]<= [MaxDate] && PBI_ELIGMEMBER[BIENDDATE] >= [MaxDate]))

During Elig - Total Completions = CALCULATE(DISTINCTCOUNT(PBI_CHALLENGES[Challenge Participants]), FILTER(PBI_CHALLENGES,PBI_CHALLENGES[CHALLENGE_COMPLETE_FLG]=1), FILTER(PBI_CHALLENGES,PBI_CHALLENGES[CHALLENGE_JOIN_DATE] >= [MinDate] && PBI_CHALLENGES[CHALLENGE_JOIN_DATE] <= [MaxDate]), FILTER(PBI_ELIGMEMBER,PBI_ELIGMEMBER[BIEFFECTIVEDATE]<= [MaxDate] && PBI_ELIGMEMBER[BIENDDATE] >= [MinDate]))

Here [MaxDate] and [MinDate] are come from the Master calendar table. And the Master calendar table has no direct relationship with Eligimember table.

Challenge Completions = SWITCH(TRUE(), SELECTEDVALUE(PBI_ELIGIBILITY_STATUS[STATUS]) = "At End of Selected Date Range",[At End Elig - Total Completions], SELECTEDVALUE(PBI_ELIGIBILITY_STATUS[STATUS])= "During Selected Date Range",[During Elig - Total Completions])

Here is a sample model with a few tables:

Please provide suggestions for any optimized measures.

Thanks,
Mohan V.

[WishAskedSooner]

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.

[MFelix]

Hi @Anonymous ,

Optimization of models and DAX measures is not that easy you need to check several parts of your code and understand where it's getting slow.

What I can tell you from the image I see is that having a many to many relationships (even with a single filter) it's not a good option to get a good performance, in this case I'm refering to the PBI_CHALLENGES  and PBI_ELIGMEMBER relationship, but I can see that you also have other relationships like this.

Optimized models with millions and millions of rows need to be in a star schema and this is not the case in this example that is also part of your issues.

Instead of doing a measure to do the Challenge completions you can try and use a Field Parameter that will allow to run each of the metrics individually instead of them running on top of a switch.

Again this is just an high level perspective of what you present, further details need to be done analizing your model closely.

[Anonymous]

Hey Thanks for your replay..
I have tried and didn't work.. Any suggestions on better versin of the measure i have used

[MFelix]

HI @Anonymous ,

Without knowing the model and the way it works it's difficult to pintpoint the error, we cannot refer please change this or that and the performance will be enhanced since there is a lot into play when refering to DAX and the Vertipaq engine.

[WishAskedSooner]

My first suggestion would be to clean up your Measures by deploying VARs. The reason for this are two-fold:

1. Makes code easier to read

2. Avoids re-calculating over and over values that only need to be calculated once in the code.

Unfortunately, while DAX is a low code language, it still presents pitfalls that require having a working knowledge of computer programming techniques.