UNLEASH the full POTENTIAL of Conditional Formatting | ROW by ROW color scale in a MATRIX in PowerBI

Рет қаралды 67,242

Күн бұрын

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 KZfaq: 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

@loriwallace3222 2 ай бұрын

Every video I watch of this man I am convinced he is a genius

@johnnickell78 2 жыл бұрын

Really appreciate the full explanation from start to finish. This was very helpful for me in trying to establish a "per column" color scale.

@sricharang9537 2 жыл бұрын

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!!!! 😊😀

@virajpatil5646 2 жыл бұрын

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

@R3W3W Жыл бұрын

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.

@anapimenta9643 2 жыл бұрын

Perfect! You don't have the idea how it totaly helped me with my current project. You are the best. Thank you so much!!!!!

@VietBaka 9 ай бұрын

Wow...I have been looking online for hours finding a solution to this. Thanks! Subscribed!

@johnabram4159 2 жыл бұрын

I must subscribe you. Although the video was about Conditional Formatting but, I never before better understood SUMMARIZE and ADDCOLUMNS concepts. Thank you.

@Kjuunaa 2 жыл бұрын

can't believe tutorial of such quality is free! Thank you!

@HowtoPowerBI 2 жыл бұрын

oh that's so nice to hear! thank you Alex! 😀😊

@se7engold Жыл бұрын

Mate, this is exactly what I was looking for. Huge thanks!

@HowtoPowerBI Жыл бұрын

Awesome! 😎

@melissaphillips8506 Жыл бұрын

Once again you have saved me. Thank you.

@user-tr2gz3ok5e 8 ай бұрын

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 8 ай бұрын

Wow, thank you! 😊thats awesome

@StefanWolfrum 2 жыл бұрын

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 😂 🤣 😂

@AliHoolash 2 жыл бұрын

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!

@stevegisselman765 2 жыл бұрын

Simply amazing! Your youtube channel is one of the best educational resources for Power Bi!

@HowtoPowerBI 2 жыл бұрын

Wow, thank you!

@stevegisselman765 2 жыл бұрын

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

@themorgelyn7382 Жыл бұрын

Brilliant vid. Thank you sooooo much

@AhnQiraji Жыл бұрын

insanely good video, thx a lot!

@unnikrishnansanthosh 2 жыл бұрын

Wonderful , thank you BAS 🥰

@garcialex06 Жыл бұрын

Such a great video!

@oricchannel2811 2 жыл бұрын

Uau !!! Another useful tip. Won my like. Thank you sharing !!!

@HowtoPowerBI 2 жыл бұрын

awesome, thx!! 😀

@humorislife8951 2 жыл бұрын

Great video Mr. Powerbiman 👨!

@JustRandomExplorer 2 жыл бұрын

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 😉

@ricksgiraldo 2 жыл бұрын

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?

@Robay146 Жыл бұрын

Thank you so much for this, Bas. Hopefully, the PBI team will one day add conditional row formatting as a native feature! lol.

@alinasamburska867 2 жыл бұрын

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!

@praveer82 Жыл бұрын

BAS, U R AWESOME BROTHER, GOD BLESS YOU

@ericet19 2 жыл бұрын

Crazy ! Keep up the great videos !

@HowtoPowerBI 2 жыл бұрын

Thank you!!!! 😊

@PedroAnacletoOficial 2 жыл бұрын

Wow, that's awesome! Thank you so much.

@HowtoPowerBI 2 жыл бұрын

who knew colors could b so exciting haha 😉😄 - thx Pepe!

@joaorataoo 2 жыл бұрын

You just "hsla" my mind :) Super clever, Bas!

@HowtoPowerBI 2 жыл бұрын

😂lol thx João!

@arameshkumar5072 2 жыл бұрын

No words from my end ❤️ , 👍🏿 pretty cool stuff 💞

@HowtoPowerBI 2 жыл бұрын

😎👊 thx!

@michauJi 2 жыл бұрын

Great material! Helped a lot

@HowtoPowerBI 2 жыл бұрын

Happy to hear this Mike!

@kartik-wi2jv 2 жыл бұрын

Always beyond expectations.. Good share.. Thank you...

@HowtoPowerBI 2 жыл бұрын

so nice - thx Bala! 😊

@codytalley6958 2 жыл бұрын

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!

@nikhargesumit9069 2 жыл бұрын

Always something out of the box. beyond the mind 👍

@HowtoPowerBI 2 жыл бұрын

thank you so much!! 😊

@TheColjackoneal 2 жыл бұрын

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)

@just1live 2 жыл бұрын

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

@scramiro1 6 ай бұрын

Gracias!! Awesome work!

@HowtoPowerBI 6 ай бұрын

gracias!!! 😁

@bravucod 2 жыл бұрын

I love your videos!

@HowtoPowerBI 2 жыл бұрын

thanks for watching again Daniel!!! 😊

@inderr7bajaj 2 жыл бұрын

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!

@FranzL608 Ай бұрын

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?

@user-ze4xc6tu4f 2 жыл бұрын

master of BI

@HowtoPowerBI 2 жыл бұрын

thank youuuu!!! 😀

@vigneshahob 2 жыл бұрын

Dude, you are super cool! Creative and Awesome..!!

@HowtoPowerBI 2 жыл бұрын

Thank you Vignesh! so many compliments, dont know what to say! 😉😊😀👊😎

@DroneEyes Жыл бұрын

Great video ... would you mind sharing the PBIX file you showed on the video?

@paulsarbu5871 2 жыл бұрын

So good!

@HowtoPowerBI 2 жыл бұрын

thank you Paul!!!! 😀

@dmitr.abakumov9753 2 жыл бұрын

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

@gentlesoulolayemi3566 Жыл бұрын

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

@buddy332 2 жыл бұрын

Very cool!

@HowtoPowerBI 2 жыл бұрын

Thank you 😊!!!

@jagadishchowdary25 Жыл бұрын

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

@guymorales Ай бұрын

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?

@filippogermi1618 9 ай бұрын

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

@Seiffer55 2 жыл бұрын

Thanks

@HowtoPowerBI 2 жыл бұрын

You are too kind!!! thank you so much Brandon! 😊😊😀😀

@marshal115 2 жыл бұрын

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😬🤗

@khalidshaik8925 2 жыл бұрын

very informative video 👌

@HowtoPowerBI 2 жыл бұрын

Thanks a lot Khalid! 😊😀

@pavankumar-hw6zb 2 жыл бұрын

Great video

@HowtoPowerBI 2 жыл бұрын

Thanks Pavan!!!

@cg24356 2 жыл бұрын

Incredible

@HowtoPowerBI 2 жыл бұрын

Thanks Clemens!!!!

@LofiLarryChill 2 жыл бұрын

is there anyway to flip it? So the low value being green and high being red? Thank you!

@StefanWolfrum 2 жыл бұрын

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?

@hemamaleni 2 жыл бұрын

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.

@SK-jv9zw 2 жыл бұрын

Can we do conditional format for matrix row headers? Let's say I have 3 rows and I need 3 different colors. Possible?

@haraldfuchs2762 2 жыл бұрын

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 (kzfaq.info/get/bejne/qNygfrZ5mOC2k5s.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!

@Acheiropoietos 4 ай бұрын

This is 2 years old 🤯 my PBI voodoo is out of date!

@stevedoherty6649 2 жыл бұрын

Masterful

@HowtoPowerBI 2 жыл бұрын

thanks Steve!!😀

@edwinmongare7054 2 жыл бұрын

Amazing

@HowtoPowerBI 2 жыл бұрын

Thank you! 😀

@e.dejong6421 2 жыл бұрын

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

@mikelawson6619 2 жыл бұрын

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.

@ricardopinxt212 2 жыл бұрын

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.

@jakereid208 9 ай бұрын

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)

@ayushkar2226 10 ай бұрын

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 10 ай бұрын

probably a bracket too much that flies somewhere at the bottom of the formula

@ayushkar2226 10 ай бұрын

@@HowtoPowerBI thanks a lot You are a saviour....Sir

@keagankemp6275 2 жыл бұрын

Legendary enough said lol

@HowtoPowerBI 2 жыл бұрын

😀👊 thanks again Keagan

@jesuseduardobittersuarez7517 2 жыл бұрын

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 -> kzfaq.info/get/bejne/fLmEnLOemtq9aHU.html