Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sonne
Helper II
Helper II

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
v-lili6-msft
Community Support
Community Support

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.

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?

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.

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.