Dax measures to create conditional formatting

[dgiacchino]

Hi, maybe someone can help me.

The measure that can be seen below, aims to give a color format to the different cells of a variable in a Matrix.

The reality is that it works, but I have a problem.

I have many variables, and when generating each measure for each variable and applying them as a format, the table takes a long time to react.

Maybe there is some way to optimize the measurement.

From already thank you very much.

Ft_Colour_Dist/min =

VAR _ValorFiltro = SELECTEDVALUE(FiltrosClassification[Indice])

VAR _AvgSession =

    AVERAGEX (

        db_session,

        CALCULATE ( [Dist/min], ALLSELECTED ( db_athlete[TFMName] ) )

    )

VAR _DstPSession =

    CALCULATE (

        SQRT (

            VARX.P (

                KEEPFILTERS ( VALUES ( db_athlete[TFMName] ) ),

                CALCULATE ( [Dist/min] )

            )

        ),

        ALLSELECTED ( db_athlete[TFMName] )

    )

VAR _ColourClassIntraSession =

    IF (

        [Dist/min] >= _AvgSession + 2 * _DstPSession,

        "#75C050",

        IF (

            [Dist/min] >= _AvgSession + 1 * _DstPSession,

            "LightGreen",

        IF (

            [Dist/min] >= _AvgSession + 0.5 * _DstPSession,

            "#FFDB41",

            IF (

                [Dist/min] >= _AvgSession - 0.5 * _DstPSession,

                "LightYellow",

                IF (

                [Dist/min] >= _AvgSession - 1 * _DstPSession,

                "#ECC596",

                IF (

                    [Dist/min] >= _AvgSession - 2 * _DstPSession,

                    "#F29C21",

                    IF ( [Dist/min] > 0, "#EFB5B9", BLANK () )

                )

            )

        )

    )))

VAR _IndRefGame =

    AVERAGE ( db_athlete[G_RF_Dist/min] )

VAR _ColorClassSessVsIRG =

    IF (

        [Dist/min]>= _IndRefGame * 1.1,

        "#75C050",

        IF (

            [Dist/min] >= _IndRefGame * 0.9,

            "LightGreen",

            IF ( [Dist/min] > 0, "#EFB5B9", BLANK () )

        )

    )

RETURN

SWITCH(

    _ValorFiltro,

    1, _ColourClassIntraSession,

    2, _ColorClassSessVsIRG,

    3,_ColourClassIntraSession)

[rubayatyasmin · Accepted solution]

Hi, @dgiacchino 

I can see that [Dist/Min] has been called in this code repetitively. How about saving this measure in a variable? And then use it. This should optimize the memory. And how about calculating the difference between [Dist/min] and [_AvgSession] then storing it in another variable then using it for further calculation? Also, Use SWITCH instead of nested IFs. 

something like this:

VAR _DistPerMin = [Dist/min]
VAR _DistDiff = _DistPerMin - _AvgSession

VAR _ColourClassIntraSession = 
    SWITCH (
        TRUE(),
        _DistDiff >= 2 * _DstPSession, "#75C050",
        _DistDiff >= 1 * _DstPSession, "LightGreen",
        _DistDiff >= 0.5 * _DstPSession, "#FFDB41",
        _DistDiff >= -0.5 * _DstPSession, "LightYellow",
        _DistDiff >= -1 * _DstPSession, "#ECC596",
        _DistDiff >= -2 * _DstPSession, "#F29C21",
        _DistPerMin > 0, "#EFB5B9",
        BLANK ()
    )

do the same for _indRefGame. Calculate the difference then use it for calculation. Also in the return statement, you can remove 3 and only use the variable. It should by default return that variable. Right? 

[rubayatyasmin · Accepted solution]

Hi, @dgiacchino 

I can see that [Dist/Min] has been called in this code repetitively. How about saving this measure in a variable? And then use it. This should optimize the memory. And how about calculating the difference between [Dist/min] and [_AvgSession] then storing it in another variable then using it for further calculation? Also, Use SWITCH instead of nested IFs. 

something like this:

VAR _DistPerMin = [Dist/min]
VAR _DistDiff = _DistPerMin - _AvgSession

VAR _ColourClassIntraSession = 
    SWITCH (
        TRUE(),
        _DistDiff >= 2 * _DstPSession, "#75C050",
        _DistDiff >= 1 * _DstPSession, "LightGreen",
        _DistDiff >= 0.5 * _DstPSession, "#FFDB41",
        _DistDiff >= -0.5 * _DstPSession, "LightYellow",
        _DistDiff >= -1 * _DstPSession, "#ECC596",
        _DistDiff >= -2 * _DstPSession, "#F29C21",
        _DistPerMin > 0, "#EFB5B9",
        BLANK ()
    )

do the same for _indRefGame. Calculate the difference then use it for calculation. Also in the return statement, you can remove 3 and only use the variable. It should by default return that variable. Right? 

[dgiacchino]

I made some modifications based on your suggestions. Thank you so much. I'll see if performance improves.
If you have any other comments, they will be welcome.


Ft_Colour_Dist/min PRUEBA =

VAR _ValorFiltro = SELECTEDVALUE(FiltrosClassification[Indice])

// Calculate the average value of [Dist/min] for each row in the "db_athlete" table,

// considering all distinct values from the "TFMName" column.

VAR _AvgSession =

    AVERAGEX (

        ALL ( db_athlete[TFMName] ),

        [Dist/min]

    )

// Calculate the standard deviation of [Dist/min] for each row in the "db_athlete" table,

// considering all distinct values from the "TFMName" column while retaining other filters.

VAR _DstPSession =

    CALCULATE (

        SQRT (

            VARX.P (

                KEEPFILTERS ( VALUES ( db_athlete[TFMName] ) ),

                CALCULATE ( [Dist/min] )

            )

        ),

        ALLSELECTED ( db_athlete[TFMName] )

    )

// Reference the measure [Dist/min].

VAR _Variable = [Dist/min]

// Calculate the difference between _Variable (assumed to be [Dist/min]) and _AvgSession.

VAR _Diff1 = _Variable - _AvgSession

// Determine the color based on the value of _Diff1, comparing it with multiples of _DstPSession

// and checking if _Variable is greater than 0.

VAR _ColourClassIntraSession =

    SWITCH (

        TRUE(),

        _Diff1 >= 2 * _DstPSession, "#75C050",

        _Diff1 >= 1 * _DstPSession, "LightGreen",

        _Diff1 >= 0.5 * _DstPSession, "#FFDB41",

        _Diff1 >= -0.5 * _DstPSession, "LightYellow",

        _Diff1 >= -1 * _DstPSession, "#ECC596",

        _Diff1 >= -2 * _DstPSession, "#F29C21",

        _Variable > 0, "#EFB5B9",

        BLANK ()

    )

// Calculate the average value of [G_RF_Dist/min] in the "db_athlete" table.

VAR _IndRefGame =

    AVERAGE ( db_athlete[G_RF_Dist/min] )

// Determine the color based on the value of _Variable, comparing it with multiples of _IndRefGame

// and checking if _Variable is greater than 0.

VAR _ColorClassSessVsIRG =

    SWITCH(

        TRUE(),

        _Variable >= _IndRefGame * 1.1, "#75C050",

        _Variable >= _IndRefGame * 0.9, "LightGreen",

        _Variable > 0, "#EFB5B9",

        BLANK ()

    )

// Determine the final output based on the value of _ValorFiltro.

VAR _Result =

    SWITCH(

        _ValorFiltro,

        1, _ColourClassIntraSession,

        2, _ColorClassSessVsIRG,

        3, _ColourClassIntraSession

    )

// Return the final result.

RETURN _Result

[rubayatyasmin]

Do we need to store the result in a variable? we are not using that statement any further to this code right? How about just using return statement? Like this

RETURN SWITCH( _ValorFiltro, 1, _ColourClassIntraSession, 2, _ColorClassSessVsIRG, _ColourClassIntraSession)

see if it improves the performance or not. Would have been better if you could find a way to make it within one switch statement. Just an Idea. 

Thanks 

Did I help? If yes, hit 👍, accept this solution as the answer. 

[dgiacchino]

In this optimized code, I have calculated the result of the SWITCH in the _Result variable and then used _Result to return the final result. This avoids duplicating the calculation of _ColourClassIntraSession and improves the overall performance of the measure.

I'm not sure this is entirely correct.

I will compare the two options.

[rubayatyasmin]

Let me know, I am curious as well

[dgiacchino · Accepted solution]

Use the two measures on 3 variables, only modifying the return option.

It is important to clarify that the data that was compared was the time it takes to load the matrix, after selecting option 1, 2 or 3.

What I could observe, after doing it several times and only having three active variables.

Using the average used by the VAR _RESULTADO, the loading times were 2037, 1638, 1732.

In the other case, the times were 2060, 1874, 1789.

Something important, that I was able to observe, is that even though it improves, the process continues to be slow.

I'm working with parameters, where I included many variables and these format measures affect performance a lot.

Another aspect that may be useful to someone who sees this is that having a dashboard, with many filters, cards, etc. It is essential to use the tool to apply the filters all together, otherwise all the modifications that affect each section are loaded many times.

[rubayatyasmin]

Performance was not that much. Can you try to do it within one Switch statement? 

Thanks

[dgiacchino]

Hi, sorry for the delay.

You mean that the two options swith, and try to generate a single one.

I'm not quite sure how I would do it. If you can generate an example I would appreciate it.