Power BI Accelerator Week #4 is Live!

All,

After the great work you’ve all done in Accelerator weeks #1 through #3, we thought you might want to kick back this week, relax, and watch a movie. But what to watch? To address this defining question of our era, perhaps we can put our analytical thinking and Power BI skills to work in finding some hidden gems within the 3,600+ movies we’ve pulled from the Internet Movie Database (IMDb).

This week’s exercise, which is really a series of sub-problems of varying difficulty, is to reconstruct my report below:

I’ve also provided a link to the live report, so you can play with it and also use the results to validate your answer:

I’ve given you a pretty good head start by building out a simple but effective star schema data model, and providing you with a series of base measures upon which you can build more complex interactions via measure branching. The attached PBIX file has detailed instructions for each analysis/visualization.

In the first three cycles of Accelerator, we spoke extensively about DAX Evaluation Context being composed of both filter context and row context, and did a deep dive into filter context. Now, we round off that discussion by delving into row context and iterating functions, so you can expect to see a lot of the latter used in your solution. Additionally, variables are a key topic this cycle and using those effectively will make the measures you create clearer, easier to debug, and likely faster as well.

Finally, given the open-ended nature of eDNA Data Challenge #15 running concurrently with this exercise, I wanted to provide some examples of how you can take an open-ended requirement and craft interesting analytical questions that hopefully provide valuable insights in addressing that requirement.

However, this is a really fascinating dataset, and the questions I’ve crafted are just a fraction of the interesting ones which can be asked. If after reproducing my report you are still looking for additional practice and challenge, add a second page to your report and include some analyses of your own. @sam.mckay and I will highlight some of the most creative and insightful additional analyses and visualizations during the live solution session.

Finally, if you choose to post your solution to social media (which we definitely encourage), please include hashtag #EnterpriseDnaAccelerator on your post.

Here’s the exercise file. Now go find something good to watch…

  • Brian

P.S. a huge thanks to the awesome Accelerator Advisory Team of @KimC , @eric_m and @quantumudit , who came up with the IMDb idea and offered extensive comments on prior versions of this exercise which improved it substantially.

Power BI Accelerator – Week #4 Final Exercise.pbix (11.2 MB)

6 Likes

All,

Per the excellent suggestion from @Keith last cycle, here are the live links to the suggested learning resources for this week’s exercise #4:

How To Use Variables in DAX Formulas with Power BI

Debug DAX using Variables in Power BI

Explaining Row Context

Iterating Functions Deep Dive - SUMX, AVERAGEX, MINX, MAXX

RANKX Considerations

Enjoy!

– Brian

4 Likes

Hi @BrianJ and all involved,

Love this Accelerator series and wether you’re part of the advisory team, a participant or leading these sessions… I just want to say “awesome job everyone” :+1: really well done :clap: :clap: :clap:

I’m also convinced this is the best and most practical “all round” quick starter guide out there for anyone who wants to get familiar with PBI and start creating PBI Reports on their own. Especially in its current format with Expert guidance - truly amazing!

3 Likes

@Melissa ,

Wow – thanks for the kind words and support! It’s been a really fun initiative, and I truly hope a lot of folks are getting benefit out of it.

Your message also reminded me of a point I meant to make in my initial post above. While we structured this series to have each week build on the past lessons, they are also self-contained enough that even if you haven’t been with us from the start, you should be able to jump in at any given week, do the exercise and learn from the solution event.

We plan to run this cycle front to back at least a couple times a year, but there’s no need to wait for the next full cycle start to jump in (and you can also always catch up on the ones you missed on video in the portal site).

Thanks again!

– Brian

4 Likes

Another fun stop on the Accelerator Train…choo choo! :bullettrain_side:

Big movie fan (not talking about my lockdown weight gain here :rofl:) so will be great to get stuck into this one!

DJ

2 Likes

Another great Accelerator week which is both challenging and interesting.

3 Likes

Hello eDNA Team and Community,
I just downloaded the exercise file and it looks like this week will be rich and challenging.

1 Like

@MehdiH ,

Thanks - I think you and the rest of the Accelerator crew will enjoy this one. I think overall it’s perhaps half a click less difficult than Week #3 (I’ve always found row context to be a bit more intuitive than filter context, probably because of decades spent in Excel which operates in a way similar to row context in DAX), but will be very interested to hear what you and others think.

Thanks very much for participating!

  • Brian
1 Like

All,

Because of a conflict with the Business Analytics Week event this coming week, we are bumping the live solution event for Accelerator Week #4 to Wednesday, September 1 at 5 PM ET. Thus, you get an extra week to work on the week #4 problem set.

I’ll have the registration link for that event posted on Monday. Have a great weekend.

  • Brian
3 Likes

@BrianJ first time getting involved in the Accelerator challenges. Week 4 has got a curve ball or 2

After feeling quite clever at sorting out the top 5 directors avg roi and number of movies I have fallen foul of the filmography column!
Thanks for the head scratcher…
Can you point in the general direction?
Please :slight_smile:

2 Likes

@stevens ,

Nice job on the top five directors ROI – that’s not easy.

With regard to the filmography, check out the incredibly versatile and underrated iterator:

CONCATENATEX

– Brian

3 Likes

@AntonyC ,

Welcome to the forum and to Power BI Accelerator! Great to have you involved in both.

Got to throw a couple of curveballs in to keep things interesting… :grinning:

But if you get really stuck, fine to come here for hints and tips. Just be sure to use the “blur spoiler” option via the gear at the top right if you’re posting something that others still working on the problem set might not want to see.

image

Good luck!

– Brian

1 Like

It is already obvious that something amazing is going on here already, let me join the train of this week #4 Accelerator.

2 Likes

@davidcenna ,

Thanks! – welcome aboard! Still plenty of room left on this train.

You can jump on at any time. If you want to catch up on previous ones as well, they are all recorded and posted here:

Enjoy!

  • Brian
2 Likes

Brian:

Does Worldwide Gross include US Gross? Or are they two separate entities?

No “Godfather” or “Goodfellas”?

John Giles

John G.,

Worldwide gross includes US gross.

WIth regard to your other question, also no Pulp Fiction, Once Upon a Time in Hollywood or Wolf of Wall Street. Kind of calls into question the integrity of the whole exercise… :grinning: (actually, the dataset was quite dirty, so rather than have you all spend your time dealing with anomalous data, I just took a broad axe to anything that I thought might have caused problems, so a lot of great movies were lost in the process…)

  • Brian
1 Like

I would just like to thank @BrianJ and his team for putting the data together. I know its hard to get correct data but everyone has to decide what to include and not include.

For me within the Accelerator Program, using proper dax, process, formulas, trying to tell a story on presentation skills against the data we received is the important thing.

Thanks
Keith

1 Like

@Keith ,

Thanks, Keith. Honestly, it’s a difficult balancing act. We’re trying to make the Accelerator problem sets as real-world as possible, but also trying not to make participating in Accelerator too time consuming or laborious. One of the places where these two objectives clash the most is with regard to data cleaning. I would LOVE to import my data into Power BI from a shiny, state of the art data warehouse, administered by a band of SQL ninjas. If that is anyone here’s reality, I am truly envious. Instead, I’ll typically be working with dirty, anomalous data like most of the rest of the world.

Learning how to effectively clean that data is an invaluable skill, and I would love for us to have the time to provide a truly raw dataset each week, have everyone clean and model it, and then begin that week’s exercise, but the time involved for most participants would be prohibitive. However, we will be returning to data cleaning, prep and shaping with a vengeance in Week #6 and again in Week #10, so we will all get the opportunity again to get our hands dirty with some very messy data…

  • Brian
4 Likes

Sounds great. I just need get my head around formula etc…i’m finding it hard again