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 somebody has an idea to optimize my calculation:

 

previous Period = if(((CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2))+0,005)<=(CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-1));
Round((CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2)*100);1)&" % " &" "&unichar(129093);
if(((CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2))-0,005)>(CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-1));
(ROUND((CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2)*100);1))&" % " &" "& unichar(129095 );
(ROUND((CALCULATE('DPA '[DPA N-3];Period[Period]="P0"&MONTH(TODAY())-2)*100);1))&" % " &" "&unichar(129094)))
 
it is not so complicated as it looks like, mainly values for current month and previous month are compared and depends on the fifference i get a trend arrow...
Need Help

5 Replies

  • 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