Introduction
Continuing with the Nobel Prize data that Meagan began exploring last week, this week extends to consider aspects of individual Nobel laureates. The main visual is a cumulative line chart, but you’ll also provide detail on laureates with a report page tooltip.
While additional JSON expansion and transformation was required from the original dataset, this week goes a bit easier on Power Query and provides a relatively clean (but not completely clean) dataset posted to Data.World.
The challenge this week builds upon some basic DAX seen in prior challenges, optionally explores Quick Measures, and then requires you to use DAX to create a measure where it might initially be tempting to create a separate column.
Using the countries of birth (or modern equivalents provided in the “now” column), this challenge allows you to better explore the geographical diversity of laureates over time.
The text on the embedded version is difficult to read, so use the arrow icon in the lower right corner to expand to full screen.
Requirements
- Create a measure that provides a distinct count of Laureates (there are individuals who have won multiple awards)
- Create a measure that provides a running total of laureates by award year (HINT: there’s a Quick Measure)
- Create a measure named “Age When Awarded” that roughly calculates age based on Award Year minus Birth Year.
- Do NOT create this as a column. The challenge is to create the Age calculation using only a measure.
- Account for a 2020 award winner who does not have a birth date listed without filtering out the record.
- It’s not a perfect calculation for someone’s age, but we’re not trying to win any prizes here…
- Add a line chart showing cumulative laureates over time by continent
- Add a zoom slider to the line chart’s Y axis
- Add a report page tooltip containing a table showing data for each individual laureate (NGOs and other non-individual entities can be excluded). Add your “Age When Awarded” measure to this table.
Dataset
This week uses a prepared version of the Nobel dataset available on Data.World. Try the built-in Power BI connector for Data.World! Like Meagan’s data from last week, its original source is the Nobel Prize API.
https://data.world/dataveld/nobel-laureates
Owner: dataveld
Dataset: nobel-laureates
Share
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
This has been so interesting and useful for me to watch. I’m new to Power Bi, don’t have a coding background and am isolated working from home with little technical help. Just adding a simple running total in Power Bi was impossible for me, so I just had to do it in Excel.
With this video I’ve realised I can add my forecast data to my graph, then right click the value and choose ‘New Quick Measure’ and choose ‘Running total’ and it writes the DAX for me. I’ll be honest, I don’t fully understand the DAX it has written, and it’s difficult to get an explanation for it thats easy to understand when I have no prior coding knowledge, but it seems to work ok so that’s the main thing. Thank you.
Glad to hear that quick measures are useful to you! I’d recommend starting with the free Introducing DAX video course to start to understand what’s going on behind the scenes! https://www.sqlbi.com/p/introducing-dax-video-course/
Hi WoW team,
Thanks for this great series on Power BI Challenges. I will study all lessons from your challenge. For this week, it is better if you can adjust the linked Youtube video to display on this page. Currently, it shows only a line of text.
Thanks for the heads up Kahn! It’s now updated 🙂
Thank you very much, you are a life saver.
Hi team,
Thanks for the great series you’ve been making. I spend every day learning from a new challenge. For this episode, I am quite lost with using AVERAGEX. Instead, I use DATEDIFF for age calculation. But when I enter the input, it doesn’t drop down the column name. Is it possible for DATEDIFF?
Hi Nam! Thanks so much for participating!
DATEDIFF works to give you the date between two columns and it stores the result as a column. This would definitely work, but the requirements asked for the result stored as a measure rather than a column, which is why David uses the AVERAGEX function (minute 14 in the video). You can use either, but DATEDIFF will add a column to your data, whereas the second method will not.
Let us know if you have other questions!
Shannon