Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DHeller
Advocate I
Advocate I

Table conditional formatting based on column not displayed

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?

1 ACCEPTED SOLUTION
joaoribeiro
Impactful Individual
Impactful Individual

@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

View solution in original post

13 REPLIES 13
Adamboer
Responsive Resident
Responsive Resident

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
Advocate I
Advocate I

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:

DHeller_0-1681290419209.png

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
Impactful Individual
Impactful Individual

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:

joaoribeiro_0-1681292277906.png

 


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
Impactful Individual
Impactful Individual

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!

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

DHeller_0-1681293528730.png

 

joaoribeiro
Impactful Individual
Impactful Individual

@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

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

DHeller_1-1681298795363.png

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

 

 

joaoribeiro
Impactful Individual
Impactful Individual

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

@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


DHeller_0-1681300170111.png

 

joaoribeiro
Impactful Individual
Impactful Individual

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

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

 

joaoribeiro
Impactful Individual
Impactful Individual

@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

@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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.