topic Re: Dax measures to create conditional formatting in DAX Commands and Tips

Dax measures to create conditional formatting

dgiacchino — Tue, 01 Aug 2023 21:34:10 GMT

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)

Re: Dax measures to create conditional formatting

rubayatyasmin — Wed, 02 Aug 2023 03:19:47 GMT

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?

Re: Dax measures to create conditional formatting

dgiacchino — Wed, 02 Aug 2023 12:54:34 GMT

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

Re: Dax measures to create conditional formatting

rubayatyasmin — Wed, 02 Aug 2023 13:38:00 GMT

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.

Re: Dax measures to create conditional formatting

dgiacchino — Wed, 02 Aug 2023 13:45:46 GMT

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.

Re: Dax measures to create conditional formatting

rubayatyasmin — Wed, 02 Aug 2023 14:42:45 GMT

Let me know, I am curious as well

Re: Dax measures to create conditional formatting

dgiacchino — Wed, 02 Aug 2023 15:25:18 GMT

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.

Re: Dax measures to create conditional formatting

rubayatyasmin — Wed, 02 Aug 2023 15:31:49 GMT

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

Thanks

Re: Dax measures to create conditional formatting

dgiacchino — Sun, 06 Aug 2023 11:55:57 GMT

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.