In this week’s Accelerator, using a dataset on worldwide videogame sales, we explore many of the different ways you can employ conditional formatting to highlight key trends, patterns and findings in your data and increase the effectiveness of your visualizations.
The expansion of conditional formatting within Power BI over the last couple of years has been one of the most valuable updates IMO, and the addition of the option to conditionally format based on Field Value (as opposed to Color Scale or Rules) allows you to leverage DAX to carry the load on conditional formatting tasks, as opposed to making you repetitively go through cumbersome interactive menus.
So, for this round of Accelerator, I’ve created a series of four mini-challenges, each employing one or more conditional formatting approaches. By the end of this lesson, you will be an advanced conditional formatting ninja, able to transform your DAX measure table into a conditional formatting powerhouse.
Detailed instructions for the mini-challenges are contained in the attached PBIX file, along with the data. Here is a Publish to Web link to my solution.
Due to the Thanksgiving holiday in the U.S. and the concurrent release of Data Challenge #17 today, we are giving you an extra week to complete this problem set, with the Live Solution Session scheduled for 5pm ET on Wednesday, Dec. 1. (signup link coming soon).
As always, thanks to @KimC and @eric_m for their outstanding input as members of the Acclerator Advisory Team, and thanks to all the Accelerator participants. (Note: even if you haven’t been participating in the past - this exercise is really a standalone and you can jump right in now).
Here are the Suggested Learning Resources for PBI Accelerator #7. Quite an interesting group I think, particularly if you’re not accustomed to doing your conditional formatting via DAX through the Field Value option.
Use of DAX/Field Value to Do Conditional Formatting In Power BI
3 Ways to Add Emojis in Power BI
Using SWITCH TRUE Logic in Power BI
Color Names Supported In Power BI Conditional Formatting
Not sure what happened here, but thanks for letting me know. I killed the prior link, republished the report, created a new PTW link and inserted it back above in the first post. Would you mind trying it again and let me know if it works okay this time? Thanks much.
What an amazing topic for this round of Enterprise DNA Power BI Accelerator!
This is really a “Super” collection of exercises that lead us to review and go through all the fundamentals of conditional formatting.
As always, I learned something new :
Hex code for transparent color (#FFFFFF00) to use in a DAX measure.
How to apply a multi-columns sort on a Table visual
Speaking about the difficulty, I confirm that the “% OF MAX GLOBAL SALES BY GENRE AND YEAR” visual was the hardest one. The easiest one, for me, was the “AVERAGE SALES PER RELEASE AND SCORES BY GENRE” as I used the “Quick measures” feature to create the two Star Rating measures.
A special kudo for the report title as it directly lead to the object of the report “Gaming”.
Wow - you just crushed this one! So many nice touches on your solution, from the way you structured your DAX to the way you organized each visual’s measures into subfolders. Well done, sir.
(and now you left yourself a whole month to work on Challenge 17 )
Glad to hear you found this exercise useful.
As always, thanks for your enthusiastic participation - much appreciated.
Detailed instructions for the mini-challenges are contained in the attached PBIX file, along with the data.
Hi Brian ,
I’m looking the PBix file and the data for this project. I just can’t find them. I found the instructions for the mini-challenges, but not the data and PBIX file.
Originally, I thought the problem set file would be too big to post on the forum which is why I used the Google Drive link instead. However, it looks like they’ve bumped the file size limit up so I went back to the first post and replaced the link with the downloadable PBIX file, which contains everything you need.
Not at all. I’m glad you mentioned it - I should have attempted to post the PBIX on the first pass, but I thought the limit was about 4.5 mb - clearly that has been significantly upgraded.
In my opinion, the objective of the Accelerators is not only to learn but also to share our knowledge and skills. So, I don’t mind if you use the solution that I proposed, I am even flattered and happy to help.
Nothing special, I’m just extracting the value of the color from the “Hex code” column of the “Max Colors” table and th “Hex code” column is already in text format.
Mhm how strange, I did the same yesterday and didn’t seem to want to work. It’s working now though, the power of a fresh pair of eyes!
Here is my submission for this week, found it very helpful for honing in the formatting skills (and in some ways made me miss SSRS for it’s formatting features )
You nailed four out of four perfectly. I really like the neon green and bright red you used in the small multiples problem - the colors really pop on that one.
)
)