No video :(

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

Подписаться 225 тыс.

Просмотров 68 тыс.

50% 1

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 RU-vid: 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

Опубликовано:

6 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 119

@loriwallace3222 3 месяца назад

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.

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

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

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

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

@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 2 года назад

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

@HowtoPowerBI 2 года назад

Awesome! 😎

@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

@melissaphillips8506 Год назад

Once again you have saved me. Thank you.

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

@praveer82 Год назад

BAS, U R AWESOME BROTHER, GOD BLESS YOU

@oricchannel2811 2 года назад

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

@HowtoPowerBI 2 года назад

awesome, thx!! 😀

@garcialex06 Год назад

Such a great video!

@unnikrishnansanthosh 2 года назад

Wonderful , thank you BAS 🥰

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

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

@humorislife8951 2 года назад

Great video Mr. Powerbiman 👨!

@michauJi 2 года назад

Great material! Helped a lot

@HowtoPowerBI 2 года назад

Happy to hear this Mike!

@joaorataoo 2 года назад

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

@HowtoPowerBI 2 года назад

😂lol thx João!

@kartik-wi2jv 2 года назад

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

@HowtoPowerBI 2 года назад

so nice - thx Bala! 😊

@arameshkumar5072 2 года назад

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

@HowtoPowerBI 2 года назад

😎👊 thx!

@nikhargesumit9069 2 года назад

Always something out of the box. beyond the mind 👍

@HowtoPowerBI 2 года назад

thank you so much!! 😊

@scramiro1 6 месяцев назад

Gracias!! Awesome work!

@HowtoPowerBI 6 месяцев назад

gracias!!! 😁

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

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

@bravucod 2 года назад

I love your videos!

@HowtoPowerBI 2 года назад

thanks for watching again Daniel!!! 😊

@Robay146 Год назад

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

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

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

@paulsarbu5871 2 года назад

So good!

@HowtoPowerBI 2 года назад

thank you Paul!!!! 😀

@user-ze4xc6tu4f 2 года назад

master of BI

@HowtoPowerBI 2 года назад

thank youuuu!!! 😀

@buddy332 2 года назад

Very cool!

@HowtoPowerBI 2 года назад

Thank you 😊!!!

@vigneshahob 2 года назад

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

@HowtoPowerBI 2 года назад

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

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

@cg24356 2 года назад

Incredible

@HowtoPowerBI 2 года назад

Thanks Clemens!!!!

@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

@khalidshaik8925 2 года назад

very informative video 👌

@HowtoPowerBI 2 года назад

Thanks a lot Khalid! 😊😀

@pavankumar-hw6zb 2 года назад

Great video

@HowtoPowerBI 2 года назад

Thanks Pavan!!!

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

@Seiffer55 2 года назад

Thanks

@HowtoPowerBI 2 года назад

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

@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

@stevedoherty6649 2 года назад

Masterful

@HowtoPowerBI 2 года назад

thanks Steve!!😀

@edwinmongare7054 2 года назад

Amazing

@HowtoPowerBI 2 года назад

Thank you! 😀

@DroneEyes Год назад

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

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

@guymorales 2 месяца назад

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?

@LofiLarryChill 2 года назад

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

@filippogermi1618 10 месяцев назад

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

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

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

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

@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

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

@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 (ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-rvpKSH3zRcg.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!

@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

@jakereid208 10 месяцев назад

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 6 месяцев назад

VAR Hue = 120 - ROUND( DIVIDE( [Total Counter] - MinValue, Range )*120, 0)

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

@Acheiropoietos 4 месяца назад

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

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

@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 -> ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FSTiPm5tY8A.html