Forum Discussion
Dax measures to create conditional formatting
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?
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.
Community ChampionHi, 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?
- 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.
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.