time consuming calculation

[Sonne]

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...

[v-lili6-msft]

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]

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]

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]

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")

[v-lili6-msft]

hi, @Sonne 

For each measure, the variables are Independent.

So for [ColorKPI1], if you want to use variables, you need to define it again.

https://docs.microsoft.com/en-us/dax/var-dax

Best Regards,

Lin