Table conditional formatting based on column not displayed

[DHeller]

I want to conditional format a table column based on a column that I do not want to display. It works for some of the lines but for others the conditional format only gets applied if I add the "unwanted" column in the visual... any idea how to fix this?

[Adamboer]

It sounds like the column you do not want to display is still being used in the background of your visual, which is why the conditional formatting is not working correctly for certain rows. To fix this, you can try removing the unwanted column from the background of the visual.

One way to do this is to go to the Fields pane, find the unwanted column, and drag it out of the visual. Another way is to go to the formatting options for the visual, navigate to the "Data colors" or "Conditional formatting" section, and make sure the unwanted column is not selected as the field to base the formatting on.

If you have already tried these methods and are still experiencing issues with the conditional formatting, it may be helpful to share more details about your data and visual so that others can better understand the issue and provide more specific guidance.

[DHeller]

Thanks @joaoribeiro for getting back to me. In my data I have a value Deliverable status with 2 values. I have created a second column that returns only the corresponding number (decimal format). Now in my table visual I want to have the first column with a green or red background depending if the value is 1 or 2 but I do not want to display the column with the 1 or 2 values:

When adding only the first column in the visual, the conditional format is applied to some lines but not all. As soon as I add the second column in the visual, it works for all lines.

[joaoribeiro]

Hi @DHeller , thanks for the explanation. Can you please share how is your conditional formatting configured?

Also, why don't you use the first column and the column assigned to the rule? This way you can set background color rules for each text value in the "Deliverable status" column. Check the example below:

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[joaoribeiro]

Hi @DHeller , probably the issue is that, without the unwanted column, the Power BI cannot assign a specific value for the column for some rows that might have multiple values for this column. You can try to use MAX, MIN, or other functions to reduce the range to only one value and avoid this erros.

Can you provide examples, so I can try to help you with the issue?

Thank you!

[DHeller]

@joaoribeiro Thanks for input. I seems I do not have the same options to configure my conditional formatting (I don't have the value only). I am using PowerBI desktop.

[joaoribeiro]

@DHeller, please try to change the "Based on field" value to your "Deliverable Status" column, and change the "Summarization" option to "First" instead of "Count". This way, you should have the option to put specific text values 🙂

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[DHeller]

@joaoribeiro The Del_Sol Proposal is the field I am interested in my case. In the conditional formatting it comes automatically with Count... I can only change summarization to count distinct. I can change to First if I select format by field value but then I do not have any option to format...

Is there someting wrong with the format of that column? It is currently set as Text data type...

[joaoribeiro]

@DHeller Thank you.

Just to understand, you are interested in changing the background color of the "Del_Sol Proposal" bases on "Deliverable Status" value, right? If so, proceeed as suggested and use the "Deliverable Status" in the conditional format.

If not, please explain again which rule you want to follow for the conditional formatting.

Looking forward to hearing from you.

[DHeller]

@joaoribeiro Sorry if I confused you! In reality I have 2 fields:
Del_Solution_Proposal and Solution proposal (value derived from the first column). Now what I would like is to display only the first column and format it based on 1 or 1-Not started --> red, 3 or 3-Completed --> green.
If I conditional format based on first column I do not have option to set 1-Not started as the criteria

If I conditional format based on second column without displaying it it does not work for all lines

[joaoribeiro]

@DHeller Thank you for making it clear! I see now. Is it possible to share the PBIX file (can be as private message if you prefer)? So I can take a look in the data model? At first glance, for me it's weird that you cannot use the first column and set specific values in the formatting.

[DHeller]

@joaoribeiro Thanks a lot but unfortunately I am not allowed to share the PBIX file

[joaoribeiro · Accepted solution]

@DHeller that's ok. Try to create a Measure to retrieve the latest status from the first column using CALCULATE/MAX, something like that. This way you will have an unique value and then I believe you will be able to use it as text in the conditional formatting.

Unfortunately I cannot help much more without the sample of data/pbix. Sorry about that!

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[DHeller]

@joaoribeiro Thanks a lot for your support. For time being I will handle with icons based on the number value rather than coloring the text values. Once I have more time I will continue investigations. Thanks again!