Power BI Conditional Text Formatting Made Easy
HTML-код
- Опубликовано: 31 май 2021
- Say hello to the other Super Data Brother - Eric! In Eric's debut episode we cover the absolute best way to create conditional text formatting in Power BI bar none. In this video you will learn:
- How to use the conditional formatting feature
- How to convert text to numbers to enable easy conditional formatting
- How to easily update formatted colors
#powerbi #businessintelligence #dataanalytics 
Наука
what if the text contains a certain word, and I want to give a number based on what the text contains, and not based on an single value?
Hey Teresa. Thanks for the Question!
If you go to the 4:00 mark and use this Dax in the calculated column instead then it should work for you:
Position Status Color=
SWITCH(
True(),
CONTAINSSTRING(data[Position Status], "Vac"), 1,
CONTAINSSTRING(data[Position Status], "Off"), 2,
CONTAINSSTRING(data[Position Status], "ill"), 3,
CONTAINSSTRING(data[Position Status], "anceled"), 4,
5
)
This uses CONTAINSSTRING() to evaluate if the [Position Status] string contains the string part you specify.
So if [Position Status] has "Vac" anywhere in the string, then you'll get a 1. "Off" you'll get a 2. So on so on.
You can just follow the rest of the video from that point on!
-Eric
@@superdatabros thank you so much for the explanation!
Glad to help!
-Eric
I wasn't aware of the "Switch" function but it's very simple to use and effective. Thanks for being awareness to it!
Man, you have 5k views but this video just saved me a ton of time. Was doing a POC that involved creating some dummy stuff in Power BI. Great job & easy explanation. Cheers & have a grand day.
Thank you so much for this! It worked like a charm!!
Your video is super helpful. I was able to color format my field based on your instruction.
thank you this man, everone else's tutorials were completely off topic
Super video! Thanks a lot! I would like to see more of Power BI videos from you!
Ask and you will receive! New PBI video premiering today!
-Eric
Eric!!, thanks a lot my friend, I was searching for this solutions for many hours, your explanation and solution is so Simple and Friendly, that I cant believe why I didn't figure out before...thanks a lot "super data brothers" you have saved me :)
Your explanation made it so simple to keep putting food on my table. Thanks!!
Great video man! Thanks so much for posting this! 👍
Good work around for this issue. Thank you so much for sharing 👍
Thank you so much! I had spent lots of time on this issue, then come across your video. voila, you helped me!
Glad it helped!
finally, was looking for this exactly!!
Thank you for this idea that is working well when we have a define number of texts to describe but I was wondering if you have another tips for text begining by Z*** to put in red or if if the text is blank/empty, then put it in red too. Thanks
Great video! I liked the way you showed the problem of trying to set up the formatting without the extra number column; this is exactly the process I went through! One question - if I want to format the whole row, is there a quick way of doing this or do I have to set up the formatting rules for each field one at a time.
Thank you for This!! Solved a major problem
perfect video, it exactly does what i wanted to do. Would data load slowly if i created a column in powerquery editor instead of just there in dax?
Great video. It was very helpful. Thank you. What about a scenario where the values are not the same. For example, a value may be Exampl*2, Example*3, and you need this value to have the same color.
thank you sooooooooooo much this was exactly what I was looking for :D
You save my life brother, Thanks!!!
Very Easy! Thank you!
Thank you for this wonderful tip. I had an issue when I published the PBI report and the table didnt show up since I didnt add the Status Num color column to the table and I dont want to display that , I got an error - The visual has unrecognized fields. Is there a way to fix this.
Thanks for the video! I wonder if this will work with other columns
Worked great. Thank you.
Thanl you! It was helpful tip.
very helpful! thanks so much!
Very Much helpful Thanks
so I would need to create extra columns for every column that I wish to conditional format text in?
Thanks brother for this video helped me a lot ❤️
This is awesome. Can we apply conditional formatting on a perticular cell, not on entire column?
This was helpful thank you!
what if i want to apply the same formatting on all the columns based on value of position status?
let's suppose I have 2 measures and concatenate in one calculation so I want to give it distinct colors in both fields. Is it possible to power bi?
I want a particular cell without build in the conditional formatting, can I do that?
It works! Thanks
How do you implement the same functionality in a Bar chart , if the Bar is Vacant then Green , if filled then Blue
Saving lives!!! Thanks!
Great Vid thanks
Great tip. thanks a lot
Hi, how can I do a wild card e.g. instead of looking for the word vacant, I want anything that starts with "vac" to highlight, so I can highlight words like vaccine vacation, etc. I tried contains and I tried switch, any help will be appreciated, thanks
Very helpful. Thank you
You're welcome!
What do I do if I want a red font if value 0 ?
Please help
Thanks, clear explanation
Thanks a lot!!!!
Thank you!
I did it as you have shown but my background is still not changing from color. That is so weird.
Is it because i am using values from 2 different excelsheets in my PowerBI?
How would we do this with a measure isnstead of column?
how would you do this but using a measure instead of a column?
hi, Really good video. How can i conditional formatting on dates? For example if the date in Power PowerBI has expired it goes red
Can I copy conditional formatting settings to another field?
Hi
.in summarization option..not showing average option...only showing counts option even after make calculated column with number...pls solve this
How to apply this colour formatting,based on a column's text to its corresponding values of different columns
I have this measure that returns text such as 50/12%
BAC+ = IF([# BAC+]>0, FORMAT([# BAC+],"0") & "/" & FORMAT([% BAC+],"0%"),"")
I want to conditionally format this measure using the following measure
Met Bac+ target =
IF(
[bac+ target]
Thank you for the video! In doing the conditional formatting (icons), I only have 2 values in the Summarization field when I select 'Rules' for formatting: Count and Count (Distinct). This leads to the calculated value always being '1'. Would you know how I could get the average option added to the Summarization field, like yours in the video?
Hey Lily. Great question!
You need to make sure you change the *Based on field* dropdown from the default of *"Count of Position Status"* to *"Position Status Color Num"* (the calculation we created in the video).
This will let you choose sum or average instead of just count.
You can go to 6:10 in the video to see me do this.
Thank you for watching! Feel free to ask any more questions and keep your eyes peeled for new videos!
-Eric
@@superdatabros Thanks for the quick response Eric! Unfortunately when I select on my "Status Color" (name of my new field) from the Based on field dropdown, it automatically changes it to "Count of Status Color". Any ideas why that might be?
@@lilykadivar5871 Hmmm. Make sure that your Status Color column is defining numbers instead of of strings. If the column contains strings, then the only summarization options are count.
So make sure that in the Dax it's
"Vacant",1,
instead of
"Vacant","1",
Remove the quotes from the number.
Cheers! -Eric
@@superdatabros You were right, it worked!!! Thanks so much! Great catch :)
Thank you.
Hi @superdatabrothers, what if I have multiple "Position Status" columns? Should I do it one column at a time?
If I'm understanding you question correctly, then yes. You'd want to apply conditional formatting to each column you want formatted.
You can have them all reference the same "Position Status Color Num" column to make things quicker.
-Eric
Wow, i can't believe this is the only way
thank you
can we bold rows ?
But the total row is appearing in my view after conditional formatting but not in your view why????
In a coloumn I have these
1(opened ticket)
3 (opened ticket)
2(closed ticket)
2(opened ticket)
4(closed ticket)
4(opened ticket)
How to give conditional formatting
In these I need to give CF on opened ticket green n red for closed
Use CONTAINSSTRING to find if the strings in the column contain 'opened' or 'closed'. Go to 4:00 and use this dax instead of what's in the video.
Position Status Color=
SWITCH(
True(),
CONTAINSSTRING(data[Position Status], "opened"), 1,
CONTAINSSTRING(data[Position Status], "closed"), 2,
3
)
Then follow the rest of the video for how to apply conditional formatting.
-Eric
How to highlight entire row?
THIS IS COOL! please - can you show me how to customize the Y-axis data labels please? i need a specific color for 1 particular item.. on the y-axis.. thx.. Cherian from Abu Dhabi .. 29.Feb.2024
All going well until conditional formatting doesnt show as an option when I try for a row
The text I am trying to "switch" are the words TRUE and FALSE. I keep getting this error - Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
Same problem here :(
How to make entire row?
use the row field
Adding a calculated column for this type of formatting is really bad idea.there are many ways to do this without using calc column.