topic Re: Format background of blank cells in matrix in DAX Commands and Tips

Format background of blank cells in matrix

EpicTriffid — Mon, 24 Oct 2022 08:23:35 GMT

Hello all!

The problem:

I am trying to get all cells specified as Actuals im the below matrix to have a grey background colour. Just for differentiation. However, I can't just default them to having zero as a zero means something different from the blank in this dataset.

The code for what I've got so far is fine for colouring in figures with actuals, but getting those blank cells under actuals filled in is boggling my brain.

Q2 Actuals BG = IF( SELECTEDVALUE(Query2[Forecast/Actuals]) = "Actuals", "#d3d3d3")

Any help?

Re: Format background of blank cells in matrix

Anonymous — Mon, 24 Oct 2022 08:34:22 GMT

hello please try:

Conditional Formatting = VAR SelectedActual = SELECTEDVALUE(Query2[Forecast/Actuals]) VAR Table = CALCULATETABLE ( Table, REMOVEFILTERS ( //All your dates example Table[Date], Table[Month] ) ) VAR summaryof = SUMMARIZE ( Table, [Date], "Actual Forcast", [amount of all actual and forcast] ) VAR T3 = FILTER ( summaryof, SelectedActual = "Actuals" ) RETURN IF( T3, "#d3d3d3")

Re: Format background of blank cells in matrix

EpicTriffid — Mon, 24 Oct 2022 08:39:20 GMT

Hey! Thanks for the reply and your time! I'm afraid I can't implement this as I'm not entirely sure what it's doing?

Re: Format background of blank cells in matrix

Anonymous — Mon, 24 Oct 2022 09:40:34 GMT

Hello

Var table is making sure all values are selected

Var summary is summarizing the table with your measure

Var t3 is filtering the summarized table into only actuals

Re: Format background of blank cells in matrix

EpicTriffid — Mon, 24 Oct 2022 11:18:05 GMT

Hi Anonymous

Unfortunately that doesn't work. It still does not show any formatting of the background against any blank values.

Re: Format background of blank cells in matrix

EpicTriffid — Mon, 24 Oct 2022 14:09:17 GMT

I can get quite close to this but i just can't seem to make the conditional formatting affect the blank cells:

I also know that technically, in the table operating behind this matrix that essentially there are no rows that relate to the blank cells, so I'm trying to make Power BI only affect what is technically "missing" from the data, but I don't know how to get there!

Re: Format background of blank cells in matrix

v-zhangti — Tue, 25 Oct 2022 05:57:45 GMT

Hi, EpicTriffid

You can try the following methods.
Sample data:

Color = IF( SELECTEDVALUE('Table'[Forecast/Actuals]) = "Actuals", "#d3d3d3")

Measure = SUM('Table'[Value])+0

Best Regards,

Community Support Team _Charlotte

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

Re: Format background of blank cells in matrix

EpicTriffid — Tue, 25 Oct 2022 08:32:37 GMT

Hi v-zhangti

Unfortunately that doesn't work either as the format of the data is closer to:

So the matrix is inferring the context of data with missing years and putting a blank in it's place, generating the below.

Using the same logic in my measures of only colouring in "Actuals" as grey still gives me the below. I can't seem to select those blanks under Actuals using DAX to make them colour grey.

Does that make sense?

Re: Format background of blank cells in matrix

EpicTriffid — Tue, 25 Oct 2022 14:18:01 GMT

Is there any help on this? I feel like I've dealt with blanks before in a matrix but cannot remember what I did?