Forum Discussion

dim123dim123's avatar
dim123dim123
Icon for Helper I rankHelper I
4 years ago

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], ...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

dim123dim123 

Can you please share the code of the 6 measures?

also please advise how your report look like

  • dim123dim123's avatar
    dim123dim123
    Icon for Helper I rankHelper I
    4 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        dim123dim123
        Icon for Helper I rankHelper I
        4 years ago

        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 ?