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!
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".
@@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!
I must subscribe you. Although the video was about Conditional Formatting but, I never before better understood SUMMARIZE and ADDCOLUMNS concepts. Thank you.
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... 😅
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!
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.
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?
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.
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
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!
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?
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!
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.
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.
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
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?
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
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
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…
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!
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 (ruclips.net/video/rvpKSH3zRcg/видео.html) .. if you are able to replicate simple example just send it over (bas.dohmen@datatraining.io)
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.
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.
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 . . .
yeah, definitely more complex then it should be - but this will probably help and point you in the right direction -> ruclips.net/video/FSTiPm5tY8A/видео.html
Every video I watch of this man I am convinced he is a genius
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.
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!
thank you so much Sricharan!!!! 😊😀
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.
Wow, thank you! 😊thats awesome
Many thanks, a business request arrived yesterday for which this technique provided an immediate solution.
can't believe tutorial of such quality is free! Thank you!
oh that's so nice to hear! thank you Alex! 😀😊
Really appreciate the full explanation from start to finish. This was very helpful for me in trying to establish a "per column" color scale.
Wow...I have been looking online for hours finding a solution to this. Thanks! Subscribed!
Awesome, Bas!! Will watch this over & over again - so much to learn!! 👏
thanks Stefan!!! 😊😄 aaah that's where all the views are coming from haha
@@HowtoPowerBI 😂 🤣 😂
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".
Took me to the edge of my chair while following this tutorial! Wow! Love it! Thanks.
lol 😀 happy to hear you enjoyed the video! thx for watching Ali!
Simply amazing! Your youtube channel is one of the best educational resources for Power Bi!
Wow, thank you!
@@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!
I must subscribe you. Although the video was about Conditional Formatting but, I never before better understood SUMMARIZE and ADDCOLUMNS concepts. Thank you.
master of BI
thank youuuu!!! 😀
Uau !!! Another useful tip. Won my like. Thank you sharing !!!
awesome, thx!! 😀
Mate, this is exactly what I was looking for. Huge thanks!
Awesome! 😎
Once again you have saved me. Thank you.
Thanks for a great tutorial! How do you adjust this measure to have one colour gradient for 0?
Great video Mr. Powerbiman 👨!
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... 😅
thank you so much for the kind words! 😊
@@HowtoPowerBI Welcome and you deserve it bro!
You just "hsla" my mind :)
Super clever, Bas!
😂lol thx João!
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!
haha thanks for watching all the way till the end 😊😀 kept the best for last 😉
I love your videos!
thanks for watching again Daniel!!! 😊
BAS, U R AWESOME BROTHER, GOD BLESS YOU
Interesting method! I currently do this but with Hex values. HSL seems much more dynamic
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)
Thank you so much for this, Bas. Hopefully, the PBI team will one day add conditional row formatting as a native feature! lol.
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.
i want to know that too.. but i havent figured it out
No words from my end ❤️ , 👍🏿 pretty cool stuff 💞
😎👊 thx!
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?
Crazy ! Keep up the great videos !
Thank you!!!! 😊
Always something out of the box. beyond the mind 👍
thank you so much!! 😊
Great material! Helped a lot
Happy to hear this Mike!
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.
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
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!
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?
Always beyond expectations.. Good share.. Thank you...
so nice - thx Bala! 😊
Is it possible to change or add conditional formatting to the field instead of the value?
Wow, that's awesome! Thank you so much.
who knew colors could b so exciting haha 😉😄 - thx Pepe!
insanely good video, thx a lot!
Such a great video!
Brilliant vid. Thank you sooooo much
is there anyway to flip it? So the low value being green and high being red? Thank you!
Great video ... would you mind sharing the PBIX file you showed on the video?
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?
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!
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.
I don't think it is possible at the moment to have a dynamic page size for the tooltip 🙄 😕 sry ..
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.
Wonderful , thank you BAS
🥰
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
thx for sharing Dmitr!! 😀
Dude, you are super cool! Creative and Awesome..!!
Thank you Vignesh! so many compliments, dont know what to say! 😉😊😀👊😎
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?
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
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
Thanks
You are too kind!!! thank you so much Brandon! 😊😊😀😀
So good!
thank you Paul!!!! 😀
Gracias!! Awesome work!
gracias!!! 😁
Great video
Thanks Pavan!!!
Very cool!
Thank you 😊!!!
Can we do conditional format for matrix row headers? Let's say I have 3 rows and I need 3 different colors. Possible?
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…
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😬🤗
Incredible
Thanks Clemens!!!!
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!
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 (ruclips.net/video/rvpKSH3zRcg/видео.html) .. if you are able to replicate simple example just send it over (bas.dohmen@datatraining.io)
@@HowtoPowerBI Thank you so much Bas. Your advice worked nicely. Problem solved!
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.
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.
CF Store =
VAR CF =
IF(
SELECTEDVALUE(dimStore[StoreName]) = " Amsterdam Store",
"Blue"
)
RETURN
CF
very informative video 👌
Thanks a lot Khalid! 😊😀
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.
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.
VAR Hue = 120 -
ROUND(
DIVIDE(
[Total Counter] - MinValue,
Range
)*120, 0)
Masterful
thanks Steve!!😀
Amazing
Thank you! 😀
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?
probably a bracket too much that flies somewhere at the bottom of the formula
@@HowtoPowerBI thanks a lot
You are a saviour....Sir
This is 2 years old 🤯 my PBI voodoo is out of date!
Legendary enough said lol
😀👊 thanks again Keagan
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 . . .
yeah, definitely more complex then it should be - but this will probably help and point you in the right direction -> ruclips.net/video/FSTiPm5tY8A/видео.html
All this just for conditional formatting 😂
Haha 😄 also wish there would be an easier solution to do this. Who knows maybe in one of the next pbi updates