Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to assign a backgroud color to certain rows in my table. However, the background color relies on 2 different criteria instead of 1, so I don't think I am able to achieve this in the conditional formatting route. Is there a way to manually select rows to have a background color?
Solved! Go to Solution.
Hey, man @taylororiger
I did test it again, I tried to recreate something that you have and used the exact same formula:
Measure = SWITCH(TRUE(),
ISBLANK(MAX('PE Relationship Detail'[Engagement Quality Reviewer Name]))&&MAX('PE Relationship Detail'[PE total fees])>500000,"yellow")
Applied to all columns and it works, the only thing that can be wrong is that make sure your blanks are actually blank, notice that in Power Query, blanks are "null". This won't appear in the Visual layer, however, make sure you can see it like that in power query:
You can replace it like this:
if you don't want to test and debug anything, you can add something like to your formula that checks either blank or empty string:
Measure = SWITCH(TRUE(),
(ISBLANK(MAX('PE Relationship Detail'[Engagement Quality Reviewer Name])) || MAX('PE Relationship Detail'[Engagement Quality Reviewer Name])="")&&MAX('PE Relationship Detail'[PE total fees])>500000,"yellow")
This should work even when you don't have your blank rows actually blank.
So try this new formula first and lemme know.
Hi, @taylororiger
I believe there is no way, how to manually select certain rows and colour them, however, you can definitely write a measure even with 10 conditions to colour your row.
Write a measure something like this:
SWTICH(TRUE(),
Table[Column]=1, "#FFF",
Table[Column]=1 && Table[Column2]=1 && Table[Column4]=50, "#0FF"
Table[Column]=64, "#DDD",
"#000")
Then, in Conditional Formatting, just use Value of Field, put your measure there and you should achieve your result.
Here's the in-depth guide:
https://xxlbi.com/blog/conditional-formatting-by-field-value-in-power-bi/
Where do I write this measure?
@taylororiger
Anywhere, just click on "New Measure" as you would for normal measure and write it, Measures don'T have to be located in the particular table in order to work.
Can you explain what the values in the measure are? This is my first time using Power Bi so I am very new to this.
@taylororiger
Simply put, here you create a measure:
You write then measure like this:
MyFirstMeasure =
SWITCH(
TRUE(),
MAX('Table'[Column])=1, "#F00", // "//" means comment, this says if column Column in table Table equals 1, return #F00 which is hexcode for Red
MAX('Table'[Column])=3 && MAX('Table'[Column2])=2 && MAX('Table'[Column4])=50, "#0F0", // if Column = 3 and Column2 = 2 and column4 = 50, color it with green
MAX('Table'[Column])=64, "#00F", // if Column = 64 color it wih Blue
"#000" //if nothing applies, color it with black
)
Copy and paste this code and modify it to your conditions - "//" means comment that I put there so you know what this step does.
Then you create table visual and go to Conditional formatting:
Then when you click OK, you will get your conditional formatting:
On the left, original data, On the Right, Colored Table:
If this helps you, please mark my answer as the solution, and thumb up my post, Thank you.
Thank you, I don;t think I understand how the measure is written/ the way you have it laid out on different lines? I also don't understand in the measure where I would put my own conditions. Could you write it exactly as it would be in the measure?
@taylororiger please, give me copyable sample data of your case and I will write it for you.
The conditions would be that the row would need to have background color if "Engagement Quality Reviewer Name" (this is a column with names) is BLANK AND "PE total fees" (this ia column with $ amounts) is >500,000
SWITCH (TRUE(),
ISBLANK('Your Table' [Engagement Quality Reviewer NAME] ) & & 'Your Table' [PE total fees"] >500000, "your color")
Sorry for not proper formatting, I am on phone.
Does the following
ISBLANK('Your Table' [Engagement Quality Reviewer NAME] ) & & 'Your Table' [PE total fees"] >500000, "your color")
go inbetween the TRUE parantheses? AKA TRUE(INSERT) ?
Also, my table does not populate when I try to reference it?
@taylororiger
The formula is exactly like this, write it exactly like this, except the table:
SWITCH (TRUE(),
ISBLANK(MAX('Here put the name of your table' [Engagement Quality Reviewer NAME] )) && MAX('Here put the name of your table'[PE total fees]) >500000, "here put the color you want to see")
I followed all of the steps and applied the conditional formatting, however, it did not highlight anything in color. Should any certain colors populate or can I write one in? Should there be two & before MAX or just one?
You have to write there what color you want to color with.
I am out of my computer today, however, if you gimme your exact data and exact outcome you want to achieve I will create the outcome for you in PowerBi, that will be the fastest was. Because it seems, there is a slight inaccuracy in our views.
I am working with confidential information, so I am unable to share the data set unfortunately.
I believe I wrote the formula correctly, for the color I just wrote "yellow" ?
Yes, just "yellow" or the hex code of that color.
I don't see an error. Do you have it Table Visual?
Can you write similar data but with not confidential data and paste her, something that would serve same purpose but with no specific data?
What is table visual?
I would but I am not sure how to do that! This is all very new to me
Hi, @taylororiger
this me trying to recreate your issue with using exact same Measure:
I used conditional formatting for both columns and when conditions are met, the rows are yellow.
I would also suggest trying some Youtube videos on how to work with Power BI as it probably saves you way more time than waiting for people to answer your questions:
Here for basics:
https://www.youtube.com/watch?v=TmhQCQr_DCA
Here for Conditional Formatting:
https://www.youtube.com/watch?v=VyOyKkjWeU0
I have crossed off qualifying information to visualize my data and also to view the measure in the screenshot below.
Does this look correct?
Below is the conditional formatting I have applied to every column;
It looks like everything matches your instructions so I ma not sure what went wrong? No rows are colored.
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |