topic Re: Help! Heat map using Matrix visual in DAX Commands and Tips

Help! Heat map using Matrix visual

Anonymous — Mon, 27 May 2024 11:00:26 GMT

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.

this pictures indicates that after completion of Quarter it should show Gray colour in coloumn and remaining coloums the gradient colour should be applied.

like same for months also.

I have used the below DAX measure to set a Field Value in Conditional Formating.

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]

VAR MinPercentage = 0 -- Define your min value for gradient calculation

VAR MaxPercentage = 1 -- Define your max value for gradient calculation

VAR ColorScale =

IF(

Percentage <= MinPercentage,

"#FA8B8B",

IF(

Percentage >= MaxPercentage,

"#48BC66",

// Calculate the gradient color between "#FA8B8B" and "#48BC66"

"#48BC66" -- Default to green if no specific color gradient is required

)

RETURN

IF(

SelectedMonthNumber = 1,

"#CCCCCC",

IF(

AND(SelectedMonthNumber = 0, Percentage = 0),

"#FA8B8B",

IF(

SelectedMonthNumber = 0 && Percentage > 0 && Percentage < 0.75,

"#FDF09F",

IF(

SelectedMonthNumber < CurrentMonthNumber,

ColorScale,

"#48BC66"

)

But It gives like this

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 02:48:27 GMT

Hi Anonymous ,

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

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 04:48:45 GMT

How can I convert above DAX measure using SWITCH function.

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 06:44:36 GMT

How can I convert above DAX measure using SWITCH function.

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 06:46:50 GMT

I used below measure and it is worked

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]

RETURN

IF( SelectedMonthNumber=1 ,

"#CCCCCC",

IF(AND(SelectedMonthNumber=0,Percentage=0), "#FA8B8B" ,

IF(SelectedMonthNumber=0 && Percentage>0 && Percentage<0.061,"#F9AFB2","#FDF09F"

)

)),

But it should take min and max percentages and should apply gradient

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 07:15:40 GMT

Hi Anonymous ,

When using field conditional formatting, the gradient does not take effect directly, you have to refine the DAX gradient yourself.

Best regards,
Community Support Team_ Scott Chang

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 08:26:35 GMT

Hi, Soory for asking..

I can't understaand, Could you please explain how can we do that

Re: Help! Heat map using Matrix visual

Anonymous — Tue, 28 May 2024 08:37:43 GMT

Hi Anonymous ,

This would be more complicated, for example, if the whole was 100, 0-10 would be one color, 10-20 would be another, and so on.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

Best regards,
Community Support Team_ Scott Chang