DAX performance. Lots of what if parameters affecting measures.

[Anonymous]

Hi all,

I have a Power BI file with a tab where there are 14 segmentations (14 what if parameters) and I have a measure that use all those segmentations:

This is the measure I have:

------------------------------------------------  

INCENTIVE FEE_TRAMO =

var agop_por = SELECTEDVALUE('%AGOP'[Value])

var por_min = SELECTEDVALUE('%min'[Value])

var por_int = SELECTEDVALUE('%int1'[Value])

var por_int2 =SELECTEDVALUE('%int2'[Value])

var por_max = SELECTEDVALUE('%max'[Value])

var IF11 = SELECTEDVALUE(IF1[Value])

var IF22 = SELECTEDVALUE(IF2[Value])

VAR IF33 = SELECTEDVALUE(IF3[Value])

VAR IF44 = SELECTEDVALUE(IF4[Value])

VAR IF55 = SELECTEDVALUE(IF5[Value])

return

SWITCH(TRUE();

agop_por<por_min || [G.O.P_TOT %]<por_min;IF11 ;

(agop_por> por_min && agop_por<por_int) || ([G.O.P_TOT %]> por_min && [G.O.P_TOT %]<por_int);IF22;

(agop_por>por_int && agop_por< por_int2) ||([G.O.P_TOT %]>por_int && [G.O.P_TOT %]< por_int2);IF33;

(agop_por>por_int2 && agop_por< por_max) ||([G.O.P_TOT %]>por_int2 && [G.O.P_TOT %]< por_max) ;IF44;

agop_por>por_max|| [G.O.P_TOT %]>por_max;IF55)

-------------------------------------------------------------

The problem is that when I change any of the what if parameters values it takes almost 2 minutes to update the visual.. how can I make more optimized my measure?  (G.O.P_TOT % is another measure from another table) 

Thank you in advance. 

Andrea.

[Stachu · Accepted solution]

in your code [G.O.P_TOT %] get's evaluated 8 times, it's better to put it in the variable like this, so it gets evaluated once:

INCENTIVE FEE_TRAMO =
VAR gop_tot_perc = [G.O.P_TOT %]
VAR agop_por = SELECTEDVALUE ( '%AGOP'[Value] )
VAR por_min = SELECTEDVALUE ( '%min'[Value] )
VAR por_int = SELECTEDVALUE ( '%int1'[Value] )
VAR por_int2 = SELECTEDVALUE ( '%int2'[Value] )
VAR por_max = SELECTEDVALUE ( '%max'[Value] )
VAR IF11 = SELECTEDVALUE ( IF1[Value] )
VAR IF22 = SELECTEDVALUE ( IF2[Value] )
VAR IF33 = SELECTEDVALUE ( IF3[Value] )
VAR IF44 = SELECTEDVALUE ( IF4[Value] )
VAR IF55 = SELECTEDVALUE ( IF5[Value] )
RETURN
    SWITCH (
        TRUE ();
        agop_por < por_min
            || gop_tot_perc < por_min; IF11;
        ( agop_por > por_min
            && agop_por < por_int )
            || ( gop_tot_perc > por_min
            && gop_tot_perc < por_int ); IF22;
        ( agop_por > por_int
            && agop_por < por_int2 )
            || ( gop_tot_perc > por_int
            && gop_tot_perc < por_int2 ); IF33;
        ( agop_por > por_int2
            && agop_por < por_max )
            || ( gop_tot_perc > por_int2
            && gop_tot_perc < por_max ); IF44;
        agop_por > por_max
            || gop_tot_perc > por_max; IF55
    )

  that's the quick and easy improvement

before we move to SWITCH statement - you basically want to assign value to a range where the lower of the [G.O.P_TOT %]  and agop_por falls, is that correct?

[Stachu · Accepted solution]

I would split the calculation like this ( I also added <= criteria instead of < in case values will be exactly same as parameter, this case wasn't covered in your solution)

INCENTIVE FEE_TRAMO =
VAR por_min = SELECTEDVALUE ( '%min'[Value] )
VAR por_int = SELECTEDVALUE ( '%int1'[Value] )
VAR por_int2 = SELECTEDVALUE ( '%int2'[Value] )
VAR por_max = SELECTEDVALUE ( '%max'[Value] )
VAR gop_tot = [G.O.P_TOT %]
VAR gop_tot_index =
    SWITCH (
        TRUE ();
        gop_tot <= por_min; 1;
        gop_tot <= por_int; 2;
        gop_tot <= por_int2; 3;
        gop_tot <= por_max; 4;
        gop_tot > por_max; 5
    )
VAR agop_por = SELECTEDVALUE ( '%AGOP'[Value] )
VAR agop_por_index =
    SWITCH (
        TRUE ();
        agop_por <= por_min; 1;
        agop_por <= por_int; 2;
        agop_por <= por_int2; 3;
        agop_por <= por_max; 4;
        agop_por > por_max; 5
    )
VAR IF11 = SELECTEDVALUE ( IF1[Value] )
VAR IF22 = SELECTEDVALUE ( IF2[Value] )
VAR IF33 = SELECTEDVALUE ( IF3[Value] )
VAR IF44 = SELECTEDVALUE ( IF4[Value] )
VAR IF55 = SELECTEDVALUE ( IF5[Value] )
RETURN
    SWITCH (
        MIN ( gop_tot_index; agop_por_index );
        1; IF11;
        2; IF22;
        3; IF33;
        4; IF44;
        5; IF55
    )

 it should have the same results as your query and be faster

[Stachu · Accepted solution]

in your code [G.O.P_TOT %] get's evaluated 8 times, it's better to put it in the variable like this, so it gets evaluated once:

INCENTIVE FEE_TRAMO =
VAR gop_tot_perc = [G.O.P_TOT %]
VAR agop_por = SELECTEDVALUE ( '%AGOP'[Value] )
VAR por_min = SELECTEDVALUE ( '%min'[Value] )
VAR por_int = SELECTEDVALUE ( '%int1'[Value] )
VAR por_int2 = SELECTEDVALUE ( '%int2'[Value] )
VAR por_max = SELECTEDVALUE ( '%max'[Value] )
VAR IF11 = SELECTEDVALUE ( IF1[Value] )
VAR IF22 = SELECTEDVALUE ( IF2[Value] )
VAR IF33 = SELECTEDVALUE ( IF3[Value] )
VAR IF44 = SELECTEDVALUE ( IF4[Value] )
VAR IF55 = SELECTEDVALUE ( IF5[Value] )
RETURN
    SWITCH (
        TRUE ();
        agop_por < por_min
            || gop_tot_perc < por_min; IF11;
        ( agop_por > por_min
            && agop_por < por_int )
            || ( gop_tot_perc > por_min
            && gop_tot_perc < por_int ); IF22;
        ( agop_por > por_int
            && agop_por < por_int2 )
            || ( gop_tot_perc > por_int
            && gop_tot_perc < por_int2 ); IF33;
        ( agop_por > por_int2
            && agop_por < por_max )
            || ( gop_tot_perc > por_int2
            && gop_tot_perc < por_max ); IF44;
        agop_por > por_max
            || gop_tot_perc > por_max; IF55
    )

  that's the quick and easy improvement

before we move to SWITCH statement - you basically want to assign value to a range where the lower of the [G.O.P_TOT %]  and agop_por falls, is that correct?

[Anonymous]

Thank you @Stachu  for your solution. Is the first time a face a performance problem with DAX so is good to know that if I call a measure inside another masure it have to evaluate it each time i call it. 

What i want to do is to assign my measure (INCENTIVE_FEE_TRAMO) a value depending on diferent "what if" parameters. It looks like that: 

It means that:

if my [G.O.P_TOT %] or my  agop_por is less than min value (6%), measure INCENTIVE_FEE_TRAMOS should be IF11 = 1%, 

if [G.O.P_TOT %] or agop_por  is between min and int1 (6%-9%) it should be IF22 (7%) ...

and so on... 

The problem is that all those min, int1,int2,max, IF11,IF22,IF33,IF44,IF55 should be variables (changeables). 

This is the only way i found to do it but if you have any suggestion or advice i would appreciate it. 

Thank you again.

Andrea.

[Stachu · Accepted solution]

I would split the calculation like this ( I also added <= criteria instead of < in case values will be exactly same as parameter, this case wasn't covered in your solution)

INCENTIVE FEE_TRAMO =
VAR por_min = SELECTEDVALUE ( '%min'[Value] )
VAR por_int = SELECTEDVALUE ( '%int1'[Value] )
VAR por_int2 = SELECTEDVALUE ( '%int2'[Value] )
VAR por_max = SELECTEDVALUE ( '%max'[Value] )
VAR gop_tot = [G.O.P_TOT %]
VAR gop_tot_index =
    SWITCH (
        TRUE ();
        gop_tot <= por_min; 1;
        gop_tot <= por_int; 2;
        gop_tot <= por_int2; 3;
        gop_tot <= por_max; 4;
        gop_tot > por_max; 5
    )
VAR agop_por = SELECTEDVALUE ( '%AGOP'[Value] )
VAR agop_por_index =
    SWITCH (
        TRUE ();
        agop_por <= por_min; 1;
        agop_por <= por_int; 2;
        agop_por <= por_int2; 3;
        agop_por <= por_max; 4;
        agop_por > por_max; 5
    )
VAR IF11 = SELECTEDVALUE ( IF1[Value] )
VAR IF22 = SELECTEDVALUE ( IF2[Value] )
VAR IF33 = SELECTEDVALUE ( IF3[Value] )
VAR IF44 = SELECTEDVALUE ( IF4[Value] )
VAR IF55 = SELECTEDVALUE ( IF5[Value] )
RETURN
    SWITCH (
        MIN ( gop_tot_index; agop_por_index );
        1; IF11;
        2; IF22;
        3; IF33;
        4; IF44;
        5; IF55
    )

 it should have the same results as your query and be faster

[Anonymous]

Thank you very much @Stachu ! Only one question, how do you know which formula is going to take more time? it's possible learn it from any book, course or something like that? I would be very interested on it. 

Thank you again 🙂 

[Stachu · Accepted solution]

My usual suspects are conditionals (IF, SWITCH, more here https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/) and iterators (X functions, like SUMX, etc.), especially nested iterators (see more here https://www.sqlbi.com/articles/optimizing-nested-iterators-in-dax/).

Variables are always helpful when you use value multiple times, and they improve readability

as for learning materials this post has lots of excellent sources:
https://community.powerbi.com/t5/Desktop/Good-Source-to-Learn-DAX/m-p/537748

for me personally the gamechanger was The Definitive Guide to DAX