UNLEASH the full POTENTIAL of Conditional Formatting | ROW by ROW color scale in a MATRIX in PowerBI
Ғылым және технология
Conditional formatting will never be the same again. In this video I show you how to unlock more potential of conditional formatting in Power BI and overcome many visualization limitations. In the example I walk you through how to use the techniques to apply a color scale to a matrix visual that restarts every row or column. Enjoy the video and subscribe for more Power BI tricks!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
00:41 Applying a color in two ways
02:13 Understanding the color model HSL
04:26 Finding the displayed maximum and minimum in a matrix
09:28 Linking the displayed values to a color in a DAX measure
13:41 Restarting the color scale every row in a matrix visual
14:51 Changing the colors of the color scale
15:32 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZread: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX
Пікірлер: 119
Every video I watch of this man I am convinced he is a genius
Really appreciate the full explanation from start to finish. This was very helpful for me in trying to establish a "per column" color scale.
I've watched this about 20 times now because everything i need to do in PowerBi right now keeps bringing me here. GG to you.
Perfect! You don't have the idea how it totaly helped me with my current project. You are the best. Thank you so much!!!!!
Thank you! This tutorial was just what I needed for a major work project I am working on! Also I am a novice self learner, so for others attempting to create this row by row formatting please ensure you have a custom date table instead of using the date table associated with your data source, I had to refer to another of Bas tutorial "DATE TABLE for Power BI using Power Query".
As usual one more great video from Bas! Now a days got a habit to first like the video and then watch it as I’m sure it’ll be anyway good. Looking forward for more videos. Great work Bas!
@HowtoPowerBI
2 жыл бұрын
thank you so much Sricharan!!!! 😊😀
Wow...I have been looking online for hours finding a solution to this. Thanks! Subscribed!
Once again you have saved me. Thank you.
can't believe tutorial of such quality is free! Thank you!
@HowtoPowerBI
2 жыл бұрын
oh that's so nice to hear! thank you Alex! 😀😊
I must subscribe you. Although the video was about Conditional Formatting but, I never before better understood SUMMARIZE and ADDCOLUMNS concepts. Thank you.
Mate, this is exactly what I was looking for. Huge thanks!
@HowtoPowerBI
Жыл бұрын
Awesome! 😎
Such a great video!
Brilliant vid. Thank you sooooo much
Took me to the edge of my chair while following this tutorial! Wow! Love it! Thanks.
@HowtoPowerBI
2 жыл бұрын
lol 😀 happy to hear you enjoyed the video! thx for watching Ali!
insanely good video, thx a lot!
Wonderful , thank you BAS 🥰
Great video Mr. Powerbiman 👨!
You are my power bi design tutor. I study all your video and your ability to teach gives me greater confident in developing power bi dashboards.
@HowtoPowerBI
7 ай бұрын
Wow, thank you! 😊thats awesome
Crazy ! Keep up the great videos !
@HowtoPowerBI
2 жыл бұрын
Thank you!!!! 😊
BAS, U R AWESOME BROTHER, GOD BLESS YOU
Wow, that's awesome! Thank you so much.
@HowtoPowerBI
2 жыл бұрын
who knew colors could b so exciting haha 😉😄 - thx Pepe!
Awesome, Bas!! Will watch this over & over again - so much to learn!! 👏
@HowtoPowerBI
2 жыл бұрын
thanks Stefan!!! 😊😄 aaah that's where all the views are coming from haha
@StefanWolfrum
2 жыл бұрын
@@HowtoPowerBI 😂 🤣 😂
Uau !!! Another useful tip. Won my like. Thank you sharing !!!
@HowtoPowerBI
2 жыл бұрын
awesome, thx!! 😀
Great material! Helped a lot
@HowtoPowerBI
2 жыл бұрын
Happy to hear this Mike!
You just "hsla" my mind :) Super clever, Bas!
@HowtoPowerBI
2 жыл бұрын
😂lol thx João!
Gracias!! Awesome work!
@HowtoPowerBI
5 ай бұрын
gracias!!! 😁
No words from my end ❤️ , 👍🏿 pretty cool stuff 💞
@HowtoPowerBI
2 жыл бұрын
😎👊 thx!
Simply amazing! Your youtube channel is one of the best educational resources for Power Bi!
@HowtoPowerBI
2 жыл бұрын
Wow, thank you!
@stevegisselman765
Жыл бұрын
@@HowtoPowerBI Is there a way to render this in a way that would work for those with color blindness? Manipulating saturation yields one grayscale color across the entire matrix. Any help would be much appreciated!
Always beyond expectations.. Good share.. Thank you...
@HowtoPowerBI
2 жыл бұрын
so nice - thx Bala! 😊
I love your videos!
@HowtoPowerBI
2 жыл бұрын
thanks for watching again Daniel!!! 😊
Always something out of the box. beyond the mind 👍
@HowtoPowerBI
2 жыл бұрын
thank you so much!! 😊
Thank you so much for this, Bas. Hopefully, the PBI team will one day add conditional row formatting as a native feature! lol.
Incredible tutorial! Thanks for sharing! Is it possible to apply any color formatting like this on a scatter plot, so it creates 4 colors if it is divided in 4 parts, or 9 color if each axis divided in 3 parts and so on?
So good!
@HowtoPowerBI
2 жыл бұрын
thank you Paul!!!! 😀
master of BI
@HowtoPowerBI
2 жыл бұрын
thank youuuu!!! 😀
Nice!, will keep this trick in my sleeve for future. Heh, one must have patience to wait until the end of the video to see the possibilities of this trick. I like it! Often there is one row with huge amounts/numbers and other amounts much smaller. In those cases the Power BI built in overall conditional color formatting is useless. Thanks again Bas for this clever trick!
@HowtoPowerBI
2 жыл бұрын
haha thanks for watching all the way till the end 😊😀 kept the best for last 😉
Very cool!
@HowtoPowerBI
2 жыл бұрын
Thank you 😊!!!
Dude, you are super cool! Creative and Awesome..!!
@HowtoPowerBI
2 жыл бұрын
Thank you Vignesh! so many compliments, dont know what to say! 😉😊😀👊😎
Thank you so much! So useful! One question: is there any possibility to do a conditional formating of Matrix Rows depending on the values? Example, I have a matrix with column Country and Values Population. I want to display Country in red if population is greater than X. Thank you in advance!
is there anyway to flip it? So the low value being green and high being red? Thank you!
Thanks
@HowtoPowerBI
2 жыл бұрын
You are too kind!!! thank you so much Brandon! 😊😊😀😀
Incredible
@HowtoPowerBI
2 жыл бұрын
Thanks Clemens!!!!
The cf measure are really really helpful. Thanks a million for sharing. I tried to apply your method to an income statement i.e. highlighting summary rows depending on the row title. I have the following cf measure: cf=switch( max([header]), "Net Sales", "#b1f1ea", ...) It works if I have only one header in the row area. If I expend the row area for a subheader, the cf does not longer work. How can I force PBI to ignore any additional fields in the row area? And, is it possible to conditionally format the rows as well?
I appreciate this thanks! I have one issue about double header column in matrix, the thing is i have a report that i need to show First month Actual, Second month actual, variance, Full year budget, reportdate, i've used ssrs to achieve this but then i need to do the same on power bi. kindly assist as always
very informative video 👌
@HowtoPowerBI
2 жыл бұрын
Thanks a lot Khalid! 😊😀
Great video
@HowtoPowerBI
2 жыл бұрын
Thanks Pavan!!!
Your skills are no less than Marco Russo and Alberto Ferrari... Definitely needs much more recognition... Absolutely brilliant... Keep posting such videos... I'm learning so many things from you... 😅
@HowtoPowerBI
2 жыл бұрын
thank you so much for the kind words! 😊
@inderr7bajaj
2 жыл бұрын
@@HowtoPowerBI Welcome and you deserve it bro!
This is a great video and I was able to implement it but I need to go from Green to Red (120 - 0) is there a way to do it?
Interesting method! I currently do this but with Hex values. HSL seems much more dynamic
@HowtoPowerBI
2 жыл бұрын
hsla and rgba also have an alpha channel for transparency so therefore i would usually for for one of those (and hsl is a bit more intuitive i think)
ciao, nice video on interesting features, one question, what if: in a matrix i have some value (referred to a filtered situation) that have minimal limit or maximal limit, how i can arrange your suggestion? in detail i have a matrix referred different location that contain different value/product, and for each product i can have a minimal value or a maximal or none, and i'd like that on matrix appear red if crossed the limit (indipendently if lower or highe) green if in the limit and black if no limit assign. thanks
Bas, Amazing content as always! I use some of your tricks in my practice and people are amazed! You’re amazing! I hope you see this, but I had a question. Let’s say when I have my users pick a race / Ethnicity in a slicer, and for example, if they select Asian, then all the visual text on the report will change to the color that I have set for Asian. Let’s say when someone picks Asian all the texts turns purple so the user knows, they are distinctly looking at Asian data. Is there a way to conditionally format my report to do that? Thank you for all you do for our community!
Masterful
@HowtoPowerBI
2 жыл бұрын
thanks Steve!!😀
Amazing
@HowtoPowerBI
2 жыл бұрын
Thank you! 😀
Bas I love your content's format! Verry fresh and simple. Can you look in your book of tricks and see if there's anything for gradients, besides some conditional format the rest look like only option is flat colors for title visualizations, shape, buttons and so on... am especially interested in gradients for area, line, column, and stacked chart visualizations.
@HowtoPowerBI
2 жыл бұрын
that's so nice to hear, thx Jose!! 😊😀 ooohh that would be nice, though I don't think gradients are possible yet .. maybe for backgrounds and titles etc you could work around it by using an image that has the gradient color
Great video ... would you mind sharing the PBIX file you showed on the video?
Speaking of colors … Is there a way in DAX to return a specific color from the currently applied color theme? How do I access the first, second, third, … color of my color theme in DAX?
Good decision! I was solving a similar problem. I have day in columns and hours in rows in my matrix and I have conversion value for every day by hour in %. And I solve CF problem for every day (Columns) thought RANX for my value during the day. Sorry for the English, it's not my native language) maybe this method help someone ) good luck
@HowtoPowerBI
2 жыл бұрын
thx for sharing Dmitr!! 😀
Can we do conditional format for matrix row headers? Let's say I have 3 rows and I need 3 different colors. Possible?
I have a problem. I have rows with student ID and columns with courses, more than one. I would like to highlight the id with pink if it is female, blue for male else orange.
Great Video! I have a question: Can we have static colors for row cells? In the example StoreName - Amsterdam Store(red), Catalog Store(green), Europe Reseller(Yellow) etc. This is to differentiate multiple metrics info in the same matrix format. Thanks.
@kathyagarza4550
Жыл бұрын
i want to know that too.. but i havent figured it out
Hey Bas, your explanations are fantastic! I built a Heat Map based on your video an it worked well. However after resorting the column field of the matrix in its table (based on a sorting column), the conditional formatting disappeared. Do you have an advice for me to solve this problem? Thank you and kind regards!
@HowtoPowerBI
2 жыл бұрын
thanks a lot Harald! 😊 oh no.. that's a bit weird - if it's just the sorting you've changed that shouldn't happen. I thing I could think of is that you set up a sorting column than applied it, but that your measure only remove the filter from the original column (and you forgot to remove the filter from the sorting column) -> a more detailed explanation here (kzread.info/dash/bejne/pKqkrbWBY924k8o.html) .. if you are able to replicate simple example just send it over (bas.dohmen@datatraining.io)
@haraldfuchs2762
2 жыл бұрын
@@HowtoPowerBI Thank you so much Bas. Your advice worked nicely. Problem solved!
Thanks Bas. What if the only row i want to highlight is ¨Dusseldorf store¨. Is that possible? I compare in a business case illness % to 20 other businesses but i like to see at a glance where in the list our business is. Is this possible? Thx.
@HowtoPowerBI
2 жыл бұрын
CF Store = VAR CF = IF( SELECTEDVALUE(dimStore[StoreName]) = " Amsterdam Store", "Blue" ) RETURN CF
Great staff! Is it possible to highlight the entire row that has multiple different measures in matrix visual? I’ve tried but since they are all different measures, I’d have to set conditional format one by one for each one of them. Really time consuming…
@HowtoPowerBI
2 жыл бұрын
thx!!! 😀 nope, I think you'll have to do it one by one until power bi adds more options to the conditional formatting options in the menu😬🤗
Hi. I have a problem that I hope you can help me with. I want to create a table with some columns in it but then have a tool tip that pops up that shows other columns not in the original table. I want the new tool tip to work just like the standard one you get in powerBI. I have got close. using a matrix in a custom tool tip and using "display in rows" to get something that looks very similar, but the page size is not dynamic - so I have to make the tool tip page big enough to accommodate the largest data output - which is not ideal. hope that A) this makes sense B) you can help !!! Mike.
@HowtoPowerBI
2 жыл бұрын
I don't think it is possible at the moment to have a dynamic page size for the tooltip 🙄 😕 sry ..
@motichoorladdu
2 жыл бұрын
You are on te right track as far as approach is concerned. I implemented a similar strategy in one of my reports. Have to agree with Bas here, the tooltip size is not dynamic. Silver lining is that if you use this report in powerbi mobile app in iPad or phone, you can hover and the tooltip stays on for you to scroll over. On browsers it is not possible yet.
This is 2 years old 🤯 my PBI voodoo is out of date!
is there a way to tweak the code to inverse the color hue? ex: for the hue max at 120, Green would be 0 Red would be 120.
@TheMoviephr3ak
5 ай бұрын
VAR Hue = 120 - ROUND( DIVIDE( [Total Counter] - MinValue, Range )*120, 0)
Hi Sir, I am copying this exact thing - but to me it is showing error "The syntax for ')' is incorrect" Can you please tell , why?
@HowtoPowerBI
9 ай бұрын
probably a bracket too much that flies somewhere at the bottom of the formula
@ayushkar2226
9 ай бұрын
@@HowtoPowerBI thanks a lot You are a saviour....Sir
Legendary enough said lol
@HowtoPowerBI
2 жыл бұрын
😀👊 thanks again Keagan
Damn this looks so good again Bas! Too bad i just cannot get it to work with my data :( CF = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( 'Fct_AVN Billing', 'Fct_AVN Billing'[Ext.Rep.], Dim_Date[Quarter] ), "Qty", [Total Quantity] ), ALLSELECTED(Dim_Date) ) Its giving me the error : "the expression refers to multiple columns. multiple columns cannot be converted to a scalar value" Seeing that you have a table that only consists of Values (with the sigma sign in front). No clue how you did that but I think it has to do with my error, because the table I refer to (Fct_AVN Billing) has normal dimensions and values. Also am not able to move these values in a separate new table unfortunately..... But I really like your conditional formatting.
I Need colum by colum color, nowhere to be foud , insanity, this is why i always go back to excel, I just need the bars in colum 1 to be blue, in colum 2 to be red, etc etc etc, nothing . . .
@HowtoPowerBI
2 жыл бұрын
yeah, definitely more complex then it should be - but this will probably help and point you in the right direction -> kzread.info/dash/bejne/eIeIy7KmZde_aKQ.html
All this just for conditional formatting 😂
@HowtoPowerBI
2 жыл бұрын
Haha 😄 also wish there would be an easier solution to do this. Who knows maybe in one of the next pbi updates