cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sonne Frequent Visitor
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
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.
Sonne Frequent Visitor
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
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
Sonne Frequent Visitor
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
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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 235 members 2,947 guests
Please welcome our newest community members: