Thanks, Brian.
Apropos of nothing, but are you a native of Baltimore?
My brother-in-law is a native of the Charm City, and their accent is similar to yours.
Regards,
John Giles
@JohnG ,
Wow – good ear! I’ve lived in the DC/Baltimore area for the past 31 years, so to the extent I’ve got a noticeable accent, that would be the one…
– Brian
All,
Per my message last night, the registration link for the Power BI Week #5 rescheduled Live Solution Session (Wed, Sept 29 at 5pm EST) is now available at:
Hope to see you there, but as always, if you can’t make it or if the time zone difference doesn’t work for you we will record the session and post it to the portal site soon after we conclude.
- Brian
All,
Recording of Accelerator #5 live solution session and @sam.mckay’s and my solution files, along with the original problem set file and copies of my slides from the live event are posted here:
Really had a good time on the live event with Sam exploring the very different paths we took to getting to our respective solutions on this problem set.
As always, thanks very much for participating. We will be back late next week with Accelerator Week #6. After our three-week DAX deep dive, we are going to take a right turn into visualization and focus on how to build a great user experience with buttons, bookmarks, page navigation, drillthrough and tooltips.
Microsoft gifted us with some amazing new button capabilities in the September 2021 PBI update, so there will be lots to discuss.
See you then…
– Brian
All,
I had planned to have the Accelerator Week #6 problem set out by the end of this week. However, I’ve been dealing with a family emergency that has required a lot of time and attention, and set my schedule back a bit. Expect to see it toward the middle or end of next week.
Sorry for the delay.
– Brian
Hi @BrianJ ,
Don’t worry about it, family is the most important and you should be worrying about that. We all understand that.
Thanks for lets us know about Accelerator Week #6 problem that its going to late.
Take care of your family and yourself.
All the best.
Keith
2 Likes
1 Like
Hi @BrianJ
I’m playing catch up. Hope you’re still answering questions for this challenge.
I’ve managed to create all the charts for the non bonus section except how to conditionally format the highlighted box in the Moving Average chart and the max and min bars in the YOY% chart.
I can’t work out how to bring up any conditional formatting box.
I’ll keep trying but if you see this, some hints would be much appreciated.
Thanks
Here is your hint for conditional formatting - these settings are found in Data Colors section of the formatting pane:
click on the fx button next to the color bar to set conditional formatting options.
you will get options for:
Color Scale - colors shading from low to high values
Rules - if value is greater than X then this color
Field Value - writing a DAX formula that returns a color based on a condition (personal favorite)
Hi @Heather
Thanks. I’m trying to find where Brian set his conditional formatting but just can’t seem to find it.
My guess was it was below for the ‘YoY% - Total Worldwide Profit’ but there’s nothing there.
Did he set each year’s colour bar manually?
@KieftyKids ,
looking at @BrianJ 's solution file, he has a conditional format under Data Colors, using a measure under field value.
this measure tells the visual what color to apply, based on the conditions set in the measure, with different color values for Min, Max and above or below 0 values.
Thank you!
1 Like
Yup, @Heather nailed it. @JarrettM was the one who initially got me using Field Value as the way to do conditional formatting, and I would say now that 99% of the time that’s what I use. It’s great because once you set the field relationship, you can tinker with named colors and hex codes till you get the look just right without ever going back into the conditional Formatting menu. You just change your DAX, hit enter and it automatically updates.
– Brian
1 Like
What a neat trick. Definitely adding this to the toolbox.
Thanks @BrianJ
If you like that, check this out - many users know that instead of hex codes in your DAX measures that feed into conditional formatting field values, you can also use color names - e.g., “Red”, “Yellow”, “Black”, etc.
But what is not commonly know is that you can also use “Papaya Whip”, “Moccasin”, “Medium Violet Red”, “Medium Spring Green”, etc. Here’s a sample:
And attached is a PBIX file containing nearly 150 color names that work in Power BI.
Pretty cool…
- Brian
Named Colors in Power BI.pbix (24.7 KB)
3 Likes
another nice option with conditional formatting using a measure is that this is currently the only way to set a transparent color.
So if you want to completely hide visual’s background based on a condition, you can just add a couple of zeros to the end of the HEX code to make a color transparent.
Example =
VAR _color = “#4D4DFF”
VAR _transparent = “#00000000”
RETURN
IF [Value] > 50, _color, _transparent)
1 Like