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.

[v-sshirivolu]

Hi @ml0911  ,

I’ve reproduced this from my end using a small sample. You can check the attached PBIX file, which includes sample department and fact tables with some zero values. The Matrix is set up so that all rows, including those with zeros, are highlighted correctly. You can use this file to test and adjust the data to match your actual scenario.

Please find the attached .pbix file.

Thank you.

[ml0911]

This did not solve the problem. The same exact thing is happening, where blank (which are forced 0 in my file) cells are not being highlighted. In the sample file you provided, the entire row (including blank cells) is not highlighted, which is what I'm asking for. 

[MFelix]

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.

[v-sshirivolu]

Hi @ml0911 ,
I didn’t end up changing the measure or logic, instead, switching the visual from a Table to a Matrix resolved the issue, this works because conditional formatting in Power BI is applied at the value level ,  and in my previous setup some cells were effectively evaluated as blanks rather than actual values, so formatting wasn’t applied, but with the Matrix visual the evaluation context changed and those values were treated correctly, allowing the formatting to apply consistently even for 0s.

[ml0911]

Please look at the photos in my post. I am already using a matrix not a table. This changes nothing

[v-sshirivolu]

Hi @ml0911 ,

I would take a moment to thank @MFelix  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[ml0911]

The problem is still not solved. No one's suggestions have helped. I cannot share the data because it is confidential information.

[MFelix]

Are you able to do a mockup file?

[Ahmedx]

try inserting &"'" instead of +0

ActualHoursZero = sum('Export'[Actual Hrs]) &"'"
or
ActualHoursZero = (sum('Export'[Actual Hrs])+0) &"'"

[ml0911]

Did not resolve the issue just replaced the 0s with ' 

Cells are still not highlighting. Thank you though

[MFelix]

Hi @ml0911 ,

What is the type of relationship you have between the tables?

Can you share a print screen of the relationships to check how is setup.

[ml0911]

Is this what you're looking for?

Thank you for your help again

[MFelix]

Hi @ml0911 ,

This explains the result you are having you need to have a one-to-many relationship for this to work in the proper way like I refered.

My question is on the Group names table the column that makes the relationship has a single value for each department or you can have several departments repeated?

[ml0911]

The column in the group names table does not have any repeated values. However, when I try to change the relationship to "one to many" PowerBI is not allowing this and automatically changes it back to "many to many".

[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.