cancel
Showing results for
Did you mean:
Frequent Visitor

## 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...
5 REPLIES 5
Community Support Team

## Re: time consuming calculation

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: time consuming calculation

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?

Community Support Team

## Re: time consuming calculation

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: time consuming calculation

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")
Community Support Team

## Re: time consuming calculation

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 235 members 2,947 guests
Recent signins: