Forum Discussion

dgiacchino's avatar
dgiacchino
Frequent Visitor
3 years ago
Solved

Dax measures to create conditional formatting

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...
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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's avatar
    dgiacchino
    3 years ago

    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.

dgiacchino's avatar
dgiacchino
Frequent Visitor
3 years ago
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's avatar
rubayatyasmin
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    dgiacchino
    Frequent Visitor
    3 years ago

    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's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Let me know, I am curious as well

      • dgiacchino's avatar
        dgiacchino
        Frequent Visitor
        3 years ago

        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.