Switch

[dim123dim123]

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 ?

[tamerj1]

@dim123dim123 

Can you please share the code of the 6 measures?

also please advise how your report look like

[dim123dim123]

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 😞 

[tamerj1]

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

[dim123dim123]

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 ? 

[tamerj1]

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

[dim123dim123]

ok thx . 

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