DAX performance in IF statements

[Anonymous]

Hi,

I am having an issue with some heavy measure to compute. I made some tests and realized that a lot of computing time is lost when it's not necessary.

Let's take this simple measure :

Test = SUMX(SUMMARIZE(NAOSCUBE;COUNTRY[CTRY ESSB];GAMME[RANGE]);IF([Multi Bus];[Test2];0))

The goal is to iterate over each row of my summarized data table and evaluate a condition. What is strange is that when I filter my page on a zone (several countries) which I know none have the [Multi Bus] condition to true it take the same time. If replace the [Test2] measure by 1 for example then the result is immediate although there were no rows at all for which the condition was true. It looks like the engine is evaluating the measure [Test2] in the IF wheter or not the condition is true.

Am I missing something here ?

[v-shex-msft]

Hi @Anonymous ,

It is hard to test/coding formula without any detail sample data. Can you please share some sample data?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

Thanks for your feedback.

I try to reproduce a dummy dataset which is way more simple than mine, still there is some data. I made the strick minimum and try to add some measure that behave like mines.

I can reproduce the issue but as the dataset is simpler the time to compute is really small. You can try to measure (I use dax studio) the time for the server to compute the Test IF & the Test IF2 measures. You will see that TEST IF2 is 2/3 times faster even when you select a zone in which no countries have the "HasMultiBus" to true.

Please do not investigate the Mix measure, I tried to reproduce heavy computation, not something actually accurate.

.pbix file

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

I think these performance issue should related to nested measures invoke and iterator functions.

HasMultiBus: it will loop whole SCT and business tables.

Mix 2: it will calculation through Data, CTRL, 'PLAY' tables.

For test if2 measure, each row calculation will invoke above measures and loop each row of correspond tables and filter by their relationship. Obviously, these calculations will spend huge amount of system resource and cause the performance issues.

I'd like to suggest you take a look at following blogs to know how to deal with that scenario:

Optimizing nested iterators in DAX

Optimizing DAX expressions involving multiple measures

Regards,

Xiaoxin Sheng