Forum Discussion
Helper ISwitch
Community ChampionCan you please share the code of the 6 measures?
also please advise how your report look like
Helper ICode 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
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
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.