Forum Discussion
Helper ISwitch
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
- tamerj1
Community Champion
Can you please share the code of the 6 measures?
also please advise how your report look like
dim123dim123Code 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_333return 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
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?
