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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dgiacchino
Frequent Visitor

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.

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)
2 ACCEPTED SOLUTIONS
rubayatyasmin
Super User
Super User

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_0-1689517080227.png

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

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.

View solution in original post

8 REPLIES 8
rubayatyasmin
Super User
Super User

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_0-1689517080227.png

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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

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. 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

Let me know, I am curious as well


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

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

 

Thanks


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors