Power BI Conditional Text Formatting Made Easy
Ғылым және технология
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
Пікірлер: 83
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?
@superdatabros
3 жыл бұрын
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
@mariamoreira9896
3 жыл бұрын
@@superdatabros thank you so much for the explanation!
@superdatabros
3 жыл бұрын
Glad to help! -Eric
Thank you so much for this! It worked like a charm!!
I wasn't aware of the "Switch" function but it's very simple to use and effective. Thanks for being awareness to it!
thank you this man, everone else's tutorials were completely off topic
finally, was looking for this exactly!!
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.
Super video! Thanks a lot! I would like to see more of Power BI videos from you!
@superdatabros
3 жыл бұрын
Ask and you will receive! New PBI video premiering today! -Eric
Your explanation made it so simple to keep putting food on my table. Thanks!!
Thank you so much! I had spent lots of time on this issue, then come across your video. voila, you helped me!
@superdatabros
Жыл бұрын
Glad it helped!
Your video is super helpful. I was able to color format my field based on your instruction.
Thank you for This!! Solved a major problem
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 :)
Great video man! Thanks so much for posting this! 👍
Thanl you! It was helpful tip.
Good work around for this issue. Thank you so much for sharing 👍
Very helpful. Thank you
@superdatabros
Жыл бұрын
You're welcome!
Very Easy! Thank you!
You save my life brother, Thanks!!!
Very Much helpful Thanks
Worked great. Thank you.
thank you sooooooooooo much this was exactly what I was looking for :D
very helpful! thanks so much!
Thanks brother for this video helped me a lot ❤️
Saving lives!!! Thanks!
This was helpful thank you!
thank you
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.
Great Vid thanks
Thank you!
It works! Thanks
Thanks a lot!!!!
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 tip. thanks a lot
Thanks, clear explanation
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.
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?
Wow, i can't believe this is the only way
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
Thank you.
This is awesome. Can we apply conditional formatting on a perticular cell, not on entire column?
so I would need to create extra columns for every column that I wish to conditional format text in?
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?
@superdatabros
3 жыл бұрын
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
@lilykadivar5871
3 жыл бұрын
@@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?
@superdatabros
3 жыл бұрын
@@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
@lilykadivar5871
3 жыл бұрын
@@superdatabros You were right, it worked!!! Thanks so much! Great catch :)
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?
How do you implement the same functionality in a Bar chart , if the Bar is Vacant then Green , if filled then Blue
what if i want to apply the same formatting on all the columns based on value of position status?
Hi @superdatabrothers, what if I have multiple "Position Status" columns? Should I do it one column at a time?
@superdatabros
2 жыл бұрын
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
hi, Really good video. How can i conditional formatting on dates? For example if the date in Power PowerBI has expired it goes red
I want a particular cell without build in the conditional formatting, can I do that?
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
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?
Can I copy conditional formatting settings to another field?
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
What do I do if I want a red font if value 0 ? Please help
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]
how would you do this but using a measure instead of a column?
Hi .in summarization option..not showing average option...only showing counts option even after make calculated column with number...pls solve this
How would we do this with a measure isnstead of column?
How to apply this colour formatting,based on a column's text to its corresponding values of different columns
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
@Kumarbehera22
2 жыл бұрын
In these I need to give CF on opened ticket green n red for closed
@superdatabros
2 жыл бұрын
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
can we bold rows ?
But the total row is appearing in my view after conditional formatting but not in your view why????
How to highlight entire row?
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.
@Joauluiz1
Жыл бұрын
Same problem here :(
How to make entire row?
@slinger420
Жыл бұрын
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.