Latest Enterprise DNA Initiatives

Power BI Accelerator Week #7 is Live!

All,

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

Enjoy!

  • Brian

Accelerator Week #7 - Problem Set Final.pbix (10.3 MB)

2 Likes

All,

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

Easy Hex Code Wrangling for Power BI

Highlighting a Row - Table:

Complex DAX Conditional Formatting Example:

Huge List of Unicode Symbols

  • Brian

Hi @BrianJ

When i click on the link (publish to the web link to your solution) its not working. i get this message

thanks Keith

@Keith ,

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.

– Brian

Hi @BrianJ ,

Works fine now :slight_smile:
Keith

1 Like

@Keith ,

Great - thanks again for your help. I think you’ll find this week’s Problem Set to be a fun one.

  • Brian

@BrianJ ,
A lot of resources here. Thanks for putting all this together.

1 Like

Hi @BrianJ,

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

Please find bellow my submission.

Best Regards.
Mehdi

1 Like

@MehdiH ,

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. :clap: :clap:

(and now you left yourself a whole month to work on Challenge 17 :wink: )

Glad to hear you found this exercise useful.

As always, thanks for your enthusiastic participation - much appreciated.

  • Brian
1 Like

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.

hi @abbi

the Link is in opening posting that Brian did (print screen of the link is below) where you can find the . Data is already in the the power bi file

i hope this help
keith

Hope you don’t mind @MehdiH , the Max Global Sales one is my last one so I’m using your solution as inspiration!

How did you get the % Max Colour measure to be text? It comes back as number for me so it won’t let me use it on the conditional formatting.

1 Like

I’m having a problem downloading the Accelerator 7 components.

What have other participants been using to download?

John Giles

@JohnG / @abbi ,

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.

  • Brian
2 Likes

Indeed you did.

Thank you, and I apologize for missing it on my first pass.

John Giles

@JohnG ,

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.

  • Brian

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.

Regards
Mehdi

1 Like

Thank you @BrianJ for these very motivating words. I’m glad you found my sublission interesting.

Unfortunately for the next few days I will be busy but I will do my best to participate in the challenge.

Regards
Mehdi

1 Like

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 :unamused:)

Thanks as always guys!

Accelerator Week #7 - Problem Set Final.pbix (10.3 MB)

2 Likes

@jamie.bryan ,

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.

Very nice job all around on this one!

– Brian

1 Like