Highlight Blank Value in Matrix Visual

[vikramkumawat]

Hi Experts,
I was trying to implement a matrix visual and apply some conditional formatting to highlight the columns with different background colors.

However, the background color is not applied to the blank values, as shown in the image. I tried adding 0 to the measure I am using, but this resulted in many extra rows and columns with a value of 0 for all countries.

I need guidance on how to avoid the issue of getting extra rows and columns or how to apply the background color to blank values.

Thanks!

[Anonymous · Accepted solution]

Hi @vikramkumawat ,

Thanks @Shivu-2000  for the quick reply and solution. Allow me to provide additional insights:

(1)We can create measures.

profits = CALCULATE(SUM(Orders[Profit]),ALLSELECTED(Orders),Orders[Country] in VALUES(Orders[Country]),YEAR(calentar[Date]) in VALUES(calentar[Date].[Year]),MONTH(calentar[Date]) in VALUES(calentar[Date].[MonthNo]))

profit color = 
VAR a = SELECTEDVALUE(calentar[Date].[Month])
RETURN SWITCH(TRUE(),
a = "January","red",
a = "March","green")

(2) Set up conditional formatting.

(3) Manually modify the column width of the Profitt Colo to hide it.

Best Regards,

Neeko Tang

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

[Shivu-2000]

Hi @vikramkumawat 
Can you provide me your data or similiar dummy data.

[vikramkumawat]

Hi @Shivu-2000 ,
Please find the PBIX file in bellow onedrive link:

Dummy data.pbix

(https://44g4h0-my.sharepoint.com/:u:/g/personal/vikramkumawat_44g4h0_onmicrosoft_com/EV8ziQQ_DwxKj8W... in above file I am able to apply background color to the cells which have any value but for blank cells i am not able to do so, also when I try to fill the blank cells with 0 by adding it to measure it adds extra columns and extra rows in the visual with all value 0.
Please suggest me any possible solution to apply background color to the column if the cell is blank or not.
Thanks!

[Shivu-2000]

Hi @vikramkumawat 
You need to select Cell element -> Background Color -> Rules
This will help you to fill the colors if it is blank.

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

[Shivu-2000]

Hi @vikramkumawat 
To format the Color column based on its field values, select Conditional formatting for the Color field, and then select Background color or Font color. In the Background color or Font color dialog box, select Field value from the Format style drop-down field.
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

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

[vikramkumawat]

Hi @Shivu-2000,
Thanks for the advice. I tried to apply the formatting as you suggested. I am using measures to display the values and also to set the background color. However, I do not have the option to use blank as zero, and blank values are not displaying any background color.
Is there any way where I can give background color to the blank cells or to place 0 only in the cell where any cell of the row have data (should not add extra rows )

Thanks !