Matrix Row Conditional Formatting

[ml0911]

I am trying to format a matrix so that the color of the row is tied to the department name. However, it is not applying the color to any values that are 0. See below:

I would like the entire row highlighted in the specified colors, even fields that are 0. I have one measure for specifying the row color and a second measure that forces blank values to be 0 because PowerBI was previously populating as blanks. See both below.

[MFelix · Accepted solution]

Hi @ml0911 ,

Apologies for asking again but there must be something in your model that is impacting the relationships or the calculations.

On a previous post you refer:

"I just removed a blank row from this table and now it allowing a "one to many" relationship, however this has not fixed the issue with the cells not highlighting."

If now you have a one to many relationship using the values from the one side and making the format has reference you should get the colors on all the rows even for blank values because the context is now the dimension table.

[ml0911]

I just removed a blank row from this table and now it allowing a "one to many" relationship, however this has not fixed the issue with the cells not highlighting.

[MFelix]

Are you able to Share a copy of the file?

[cengizhanarslan]

Instead of fixing blanks in a separate step, make the displayed measure itself return 0:

Displayed Value =
COALESCE ( [Your Measure], 0 )

ALso your color logic is fine, but use it as Format by: Field value on the values in the matrix.

[ml0911]

I am using it as Format by: Field value. That's how I'm able to change the colors of the rows.

As far as the coalesce function, how is that any different from the +0 method? You're still fixing blanks in a separate step with an additional measure.

[ml0911]

I'm not really sure what a fact table or dimension table is. Sorry. The group names table is a separate table from the one where the matrix is pulling the 'Actual Hrs' values from. They are linked by the department name value. 

[MFelix]

Hi @ml0911 ,

The fact table is where you have all the information for you calculations in this case the Export Table and  Dimension tables describe business entities.

Check this link about this concepts (https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#star-schema-overview)

Concerning the relationship are they done with a one to many relationship?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[ml0911]

Thank you for the explanation. That makes sense. It is a "many to many" relationship. I cannot share the data as it is confidential. Do you know of a workaround for this issue?

[MFelix]

Hi @ml0911 ,

Is the Group Names your Fact table or is it a dimension table connected to your fact? This usually happens because the result of the query is an empty value and does not shows in the "virtual table" that is part of visual.

In the examples below I have the same matrix however one is with the values from the fact and the other is the values from the dimension:

What happens is altough you have a value of 0 in the visualization in this case the value Brazil does not exist in the fact table so no result is given for the condittional formatting.