Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Saga
Helper I
Helper I

switch case optimization

Hello,

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.

 

mseaure% = 
var x = selectedvalue(comparisonbasis[value])

RETURN
SWITCH( TRUE()
, x = "E" && [m1] = 0,
, x= "E" && [m1] <> 0 && [m3]= 0, 0
, x= "E", [m4] / [m1]
 
, x = "B" && [m2] = 0, 1
, x= "B" && [m2] <> 0 && [m3]= 0, 0
, x= "B", [m4] / [m2]
 
m1,m2,m3,m4 are all measures.
there is also a filter on the visual where iam excluding blanks.
For context of what iam trying to achieve through the measure
Saga_0-1677735457596.png

 

Any suggestions or help is appreciated.
6 REPLIES 6
Saga
Helper I
Helper I

@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.

Hi Saga, what is in the measures M1,2,3&4?

 

Pi

m1 = sum(table[col1]) and m2 = sum(table[col2]) and m3 = sum from 3 other columns and
m4 = m3 - (m1 or m2) -- depending on the selected value

Hi Saga

 

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?

Pi

@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. 

PiEye
Resolver II
Resolver II

Hi @Saga 

 

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))

 

 
This is the result I am getting:
 
PiEye_0-1677747883229.png

 

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

 

HTH!

 

Pi

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.