Help! Heat map using Matrix visual

Question

I have a Matrix visual that shows percentage % in Qtuarterly and monthly in Excel as bolow and I have applied gradient colounr in Excel like below.&nbsp;this pictures indicates that after completion of Quarter it should show Gray colour in coloumn and remaining coloums the gradient colour should be applied.&nbsp;like same for months also.&nbsp;I have used the below DAX measure to set a Field Value in Conditional Formating.&nbsp;BackgroundColor =VAR CurrentMonthNumber = MONTH(TODAY())VAR SelectedMonthNumber = MAX('M_DATE_D'[Ter Month])VAR Percentage = 'S_SPL_QMR_DO_WEEKLY_2024_F'[Offer COS/24] &nbsp;VAR MinPercentage = 0 -- Define your min value for gradient calculationVAR MaxPercentage = 1 -- Define your max value for gradient calculationVAR ColorScale =&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; Percentage &lt;= MinPercentage,&nbsp; &nbsp; &nbsp; &nbsp; "#FA8B8B",&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Percentage &gt;= MaxPercentage,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "#48BC66",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Calculate the gradient color between "#FA8B8B" and "#48BC66"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "#48BC66" -- Default to green if no specific color gradient is required&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )RETURNIF(&nbsp; &nbsp; SelectedMonthNumber = 1,&nbsp; &nbsp; "#CCCCCC",&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; AND(SelectedMonthNumber = 0, Percentage = 0),&nbsp; &nbsp; &nbsp; &nbsp; "#FA8B8B",&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SelectedMonthNumber = 0 &amp;&amp; Percentage &gt; 0 &amp;&amp; Percentage &lt; 0.75,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "#FDF09F",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SelectedMonthNumber &lt; CurrentMonthNumber,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ColorScale,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "#48BC66"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))&nbsp;&nbsp;But It gives like this&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Try changing the IF statement to a SWITCH statement, as the IF statement may have dependencies that cause conditions to not match exactly.
SWITCH function (DAX) - DAX | Microsoft Learn
&nbsp;
Hope it helps!
&nbsp;
Best regards,Community Support Team_ Scott Chang
&nbsp;
If this post helps then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

anonymous · Answer

How can I convert above DAX measure using SWITCH function.

anonymous · Answer

How can I convert above DAX measure using SWITCH function.

anonymous · Answer

I used below measure and it is worked&nbsp;BackgroundColor = ---- VAR CurrentMonthNumber = MONTH(TODAY())&nbsp;VAR SelectedMonthNumber = MAX('M_DATE_D'[Ter Month]) &nbsp;VAR Percentage = 'S_SPL_QMR_DO_WEEKLY_2024_F'[Offer COS/24] &nbsp;&nbsp; RETURN&nbsp; &nbsp;IF( &nbsp; &nbsp;SelectedMonthNumber=1 , &nbsp; &nbsp; &nbsp; &nbsp;"#CCCCCC",&nbsp; &nbsp; &nbsp; IF(AND(SelectedMonthNumber=0,Percentage=0), "#FA8B8B" , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(SelectedMonthNumber=0 &amp;&amp; Percentage&gt;0 &amp;&amp; Percentage&lt;0.061,"#F9AFB2","#FDF09F" &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;) &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)),&nbsp;But it should take min and max percentages and should&nbsp; apply gradient&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
When using field conditional formatting, the gradient does not take effect directly, you have to refine the DAX gradient yourself.
&nbsp;
Best regards,Community Support Team_ Scott Chang

Forum Discussion

Help! Heat map using Matrix visual

7 Replies

Related content

Need help on matrix visual

Help with Matrix table visual

Matrix visual help

Matrix Help

Matrix Visual Duality