Iam trying to use a switch case statement but getting out of memory issue for a visual.
I have a measure with switch statement that is causing the issue and not sure how i can optimize it.
var x = selectedvalue(comparisonbasis[value])
Any suggestions or help is appreciated.
@PiEye Thank you for re arranged logic, though it doesnt solve my problem, the visual shows data without being out of memory. But it is still alot slower. Taking more than 1min.
A small improvement could be made by hardcoding a calculated column with the sum of 3 columns and then m3 can be the sum of that.
With regards to M4, let's say selected value is "B". If you consider this part of the measure: [m4] / [m2]
This is now: ([m3]-[m2])/[m2]
Which also equals [m3]/m2 -1
How many rows are we talking?
@PiEye 100k as of now, but will increase as time goes by. My visual has to display m1,m2, m3,m4 and the measure% in a matrix. So i dont think replacing m4 with m3 - m2 will do any good here, but will add the 3 columns to cal column. Might not be much improvement but will give a try.
I'm not sure how to optimise in a technical way, per se, but I was able to re-arrange the logic in to make it simpler and less steps.
I've taken advantage of the Divide function in DAX (https://learn.microsoft.com/en-us/dax/divide-function-dax ) which has an option for expressions that result in infinity, IE they are division by zero.
Try this as a measure
Measure 2 = var denom= if(SELECTEDVALUE('Case Logic'[X])="E",[M1],[M2]) return if(denom <>0 && [M3]=0,0, divide([M4], denom , 1))
To optimise even further, you could take a look at the expressions m1->4 and perhaps hard code something in the power query model reducing the load in DAX even further
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.