Forum Discussion

Sonne's avatar
Sonne
Helper II
7 years ago

time consuming calculation

I have a  measure which takes a lot of time to calculate and the value in matrix is not really coming up. It works , but if people have to wait 5 min to see the values they are not happy. maybe some...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

HI, Sonne 

I think if you need to optimize the formula of 'DPA '[DPA N-3],

Not sure if it is possible, could you try this formula as below:

previous Period =
VAR a =
    CALCULATE ( 'DPA '[DPA N-3], Period[Period] = "P0" & MONTH ( TODAY () ) - 2 )
VAR b =
    CALCULATE ( 'DPA '[DPA N-3], Period[Period] = "P0" & MONTH ( TODAY () ) - 1 )
RETURN
    IF (
        ( ( a ) + 0.005 ) <= ( b ),
        ROUND ( ( a * 100 ), 1 ) & " % " & " "
            & UNICHAR ( 129093 ),
        IF (
            ( ( a ) - 0.005 ) > ( b ),
            ( ROUND ( ( a * 100 ), 1 ) ) & " % " & " "
                & UNICHAR ( 129095 ),
            ( ROUND ( ( a * 100 ), 1 ) ) & " % " & " "
                & UNICHAR ( 129094 )
        )
    )

and if possible, please share your sample pbix file for us have a test.

 

Best Regards,

Lin

 

  • Sonne's avatar
    Sonne
    Helper II
    7 years ago

    Hello,

    thanks for the answer. I tried the formula and it works, but it is not really faster . I think so too, that it is coming from DPA calculation.

     

    Other question : all my measures are in one table. if i choose a measure and insert this into a matrix, are the other measures also calculated ( the one in the table but not in the matrix visualisation?)

    does it make a difference if i have 1 table with all measures or more tables with the measures?

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, Sonne 

      If the measure is nested in the current measure which is in matrix visual, it will be calculated two.

      For example: in your case, [previous Period] and [DPA N-3] are two measure, and although  [DPA N-3] is not in the matrix, but it is nested in [previous Period], so it is calculated in matrix too.

       

      Best Regards,

      Lin

       

      • Sonne's avatar
        Sonne
        Helper II
        7 years ago

        Hm ok I understand. if i take now your formular:

        previous Period = VAR a = CALCULATE ( 'DPA '[DPA N-3]; Period[Period] = "P0" & MONTH ( TODAY () ) - 2 ) VAR b = CALCULATE ( 'DPA '[DPA N-3]; Period[Period] = "P0" & MONTH ( TODAY () ) - 1 ) RETURN IF ( ( ( a ) + 0,005 ) <= ( b ); ROUND ( ( a * 100 ); 1 ) & " % " & " " & UNICHAR ( 129093 ); IF ( ( ( a ) - 0,005 ) > ( b ); ( ROUND ( ( a * 100 ); 1 ) ) & " % " & " " & UNICHAR ( 129095 ); ( ROUND ( ( a * 100 ); 1 ) ) & " % " & " " & UNICHAR ( 129094 ) ) )
         
        I have here var a and var b . Can I use this variables in the other measures where i also use this caluclation.
        in this formular for excample:
        ColorKPI1 = switch(true();
        (CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2))+0,005<=(CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-1));"#198c19";
        (CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2))-0,005 > CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-1);"#b20000";"#b2ac00")