Unable to conditionally format blank cells in matrix (from nulls) based on other (populated) column.

[tgardner]

Column A has certain null values represented as blank in the matrix.

Column B, which for the sake of demonstration, is [ if [Column A] is null then 1 else 0 ]

I'm trying to apply conditional formatting on Column A values within the matrix based upon Column B.

Whilst I am able to apply the 'if value is blank', but not able to apply 'if value is 1'

When I replace Column A nulls with a numerical value, and change conditonal formatting appropriately, the formatting works...so I know it's to do with nulls. But then the table displays arbitrary numbers when I need them to remain blank.

Any solutions would be amazing!

Thanks!!

[Anonymous]

HI @tgardner,

I'd like to confirm if these blank cells are caused by not existed the corresponding records in your table of current category combines. If this is a case, they can't simply use if statement formula to check and replace beascue these records not really stored in your table.

For this scenario, you need to extract the category fields to a new table who not excited whole matched records in your table.

Then you can use this to replace the original field and write measure formula to use current category value to lookup value and return not exist records with default values that you wanted.

If you still confuse how to achieve these, please share some dummy data with the expected result to help us clarify your data structure and test to coding formula and build the sample.
Regards,
Xiaoxin Sheng

[amitchandak]

@tgardner ,

if [Column A] =null then 1 else 0, should work in power query.

In dax you can create color measure and use that conditional formatting using "field value" option. But I doubt you can color blank there too

example measure

Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red")

Color Date =
var _min =minx(allselected(Date,Date[Year])
return
 Switch( true(),
 FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen",
  FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue",
 "red")

if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")

if(FIRSTNONBLANK(date[date],blank())= tofay(),"green","red")
if(max(date[date])= tofay(),"green","red")

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Greg_Deckler]

@tgardner - I'm not clear on this. You should be able to setup conditional formatting Rules based and use "is" 0 or 1.

[tgardner]

You would think so, but absolutely not the case.

As I say, testing different scenarios and replacing the nulls in the base table with numeric values removes the issue with formatting the matrix. But for as long as they remain blank (nulls in the base table), formatting based on Column B does absolutely nothing (unless the formatting is 'if is blank')

Mystery