topic Re: Switch in DAX Commands and Tips

Switch

dim123dim123 — Wed, 20 Apr 2022 10:41:04 GMT

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 ?

Re: Switch

tamerj1 — Wed, 20 Apr 2022 11:22:38 GMT

dim123dim123

Can you please share the code of the 6 measures?

also please advise how your report look like

Re: Switch

dim123dim123 — Wed, 20 Apr 2022 11:33:20 GMT

Code is huge and allmost same for 6 measures.

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 😞

Re: Switch

tamerj1 — Wed, 20 Apr 2022 11:58:58 GMT

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?

Re: Switch

dim123dim123 — Wed, 20 Apr 2022 12:26:45 GMT

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 ?

Re: Switch

tamerj1 — Wed, 20 Apr 2022 13:03:50 GMT

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.

Re: Switch

dim123dim123 — Wed, 20 Apr 2022 13:15:15 GMT

ok thx .

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