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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dim123dim123
Helper I
Helper I

Switch

Hi i have 

 

vAR s=max( 'Account Schedule'[ScheduleName] )
VAR s1=SWITCH(
TRUE( ),
--s = "ARU1", [Balance_1],
--s = "ARU2", [Balance_2],
--s = "ARU3", [Balance_3],
--s = "ARU4", [Balance_4],
--s = "ARU5", [Balance_5],
s = "ARU6", [Balance_6],
CALCULATE( [GL Net Change], FILTER( ALL( 'dim Date' ), 'dim Date'[Date] <= MAX( 'dim Date'[Date] ) ) )

 

 


)
RETURN s1

 

This switch choose Measure that depends what kind of report Type are selected .
Why when i uncomment all lines , process time is  about 2 minutes ?

 

How can i optimize/solve this ?

 

6 REPLIES 6
tamerj1
Super User
Super User

@dim123dim123 

Can you please share the code of the 6 measures?

also please advise how your report look like

Code is huge and allmost same for 6 measures. 

 like 

VAR GL_tmp_7 =
CALCULATE(
SUM( 'fact Transaction'[amount] ),
FILTER( ALLEXCEPT( 'Account Schedule', 'Account Schedule'[ScheduleName] ), 'Account Schedule'[LineNo] IN { 7 } ),
FILTER( ALL( 'dim Date' ), 'dim Date'[Date] <= MAX( 'dim Date'[Date] ) )
)

... 

VAR GL_Net_Change_New =
SWITCH(
TRUE( ),
max( 'Account Schedule'[LineNo] ) = 7, GL_tmp_7,
max( 'Account Schedule'[LineNo] ) = 333, GL_tmp_333

 

return GL_Net_Change_New

But my question is why switch calculate code for all conditions ? or why so slow ? each switch line work correctrly and fast. 

I tried : IF ( s = "ARU6", [Balance_6], if (s = "ARU5", [Balance_5],

same result slow . seems engie  calculate  each line conditions measure 😞 

 

 

 

@dim123dim123 

It does not but your calcutions are too heavy. Many things can be improved. For example as long as the 6 measures have very similar structure then I would advide to have their codes directly implemented inside the final measure. This way you can avoid multiple scans of the same table and duplicated calculations. I can give more details on how to do that. 
the other important thing is filter ALL Date Table. Why do you need to filter it all?

sorry 6 measures have similar structure code but not same . 

I need calculate dynamic account sheduler report based on user defined totaling rules 

but why this code is  fast 

VAR s=max( 'Account Schedule'[ScheduleName] )

return if ( s = "ARU1", [Balance_1], CALCULATE( [GL Net Change], FILTER( ALL( 'dim Date' ), 'dim Date'[Date] <= MAX( 'dim Date'[Date] ) ) ) 

 

and similar almost 2 times slower 

VAR s=max( 'Account Schedule'[ScheduleName] )

return if ( s = "ARU1", [Balance_1], 

if (  s = "ARU2", [Balance_2], 

 

CALCULATE( [GL Net Change], FILTER( ALL( 'dim Date' ), 'dim Date'[Date] <= MAX( 'dim Date'[Date] ) ) )

 

 

 

 

how can i create somthing like this : 

VAR s = MAX( 'Account Schedule'[ScheduleName] )
return  IF(s == "ARU1", [Balance_1])

return  IF(s == "ARU2", [Balance_2])

 

or something like this ? 

 

 

@dim123dim123 

The conditions need to be checked one by one. Checking requires the evalution of the condition calculation. The evaluation continues one by one until the first codition is satisfied. Only then, the rest of the conditions will be igoned. 
Your cide requires optimization. And of tge steps is refrain from reffering to measures and rewrite the measure completely from scratch. This will be painful but afterall is necessary to have a healty report. when doing that, vertual tables will be created only once and then stored in a variable to be used over annd over again to evalute all the conditions. 

ok thx . 

i just generate all  dynamic measures by python without any IF or switch and deploy every night over XMLA . 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.