Performance improvement


#1

I have been struggling with performance issues when using “Switch” in my dax expression. Any suggestions on writing optimized dax to improve performance will be really of great help!. Thanks


#2

Usually SWITCH is never really an issue. It’s just like a nested IF statement. It works in a very simple and sequential manner, so I have doubts whether this is actually the problem.

Can you actually add the formula you are using though to check it out further.

Do you have measures working within SWITCH - this is more likely the problem.


#3

Here is a sample of the Switch case.

Average Crude Per Region = Switch(SELECTEDVALUE(‘Region_Table’[City]),
“Napier”,
CALCULATE(AVERAGE(Sales_Data[Revenue]), Sales_Data[WareHouseName] = “xxxx yyyy zzzz aaa bbbb cccc dddd eeeee ffff iiiii”, Sales_Data[Channel] = “Wholesale”, Sales_Data[CustomerName] = “xxxxxxxx”)
,
“Hamilton”,
CALCULATE(AVERAGE(Sales_Data[Revenue]), Sales_Data[WareHouseName] = “xxxx yyyy zzzz aaa bbbb cccc dddd eeeee ffff iiiii”, Sales_Data[Channel] = “Distribution”, Sales_Data[CustomerName] = “yyyyyyyy”)
,
CALCULATE(AVERAGE(Sales_Data[Revenue]), Sales_Data[WareHouseName] = “xxxx yyyy zzzz aaa bbbb cccc dddd eeeee ffff iiiii”, Sales_Data[CustomerName] = “zzzzzzz”)

)

Basically, there are 2 such measures I am using per chart and a single page contains 6 charts, 3 slicers and a grid. This runs on data of 300k rows. Thanks


#4

Ok first thing to do here is really explore the measure branching technique I discuss a lot.

You see with your formula you’re having to write out these long formulas within the SWITCH. This is not necessary if you build things up and it’s way more optimal to do this.

See below

Also once you’ve simplified things there try using SWITCH( TRUE(), logic instead. I think this is a better way to right this out.

See below for technique on this


#5

Thank you Sam, I watched the mentioned videos. It will try and implement these techniques into my measure. This is a great way to improve readability of the measures. However, I am still wondering if this improves performance. Can you help me understand the various aspects where it could affect the performance in Power BI.


#6

It’s hard to say exactly as just looking at the formula doesn’t really tell me the full story.

I’m interested to see what happens when you make the changes.

Most likely it’s that within the CALCULATE functions there are many filters that are happening and if the data is large then it’s probably having to iterate through millions of rows of information many times over which is causing things to work slower.

Making some of these changes could improve things and are best practice so I’m keen to see what works, but as I say it’s hard to say exactly because it could be other things including relationships the data etc.

Sam