Introduction
The November 2023 release of Power BI Desktop includes the new button slicer visual. This visual can currently be used as a replacement for the original slicer visual when using tiles. There are more enhancements to come, but we already have a lot more control over the formatting of the slicer. We can add images, change saturation, add a blur, and more.
This week, we are using the button slicer to examine the age of Academy Award winners of the best actor, best actress, and best director awards.
Requirements
1. Import the data provided for this exercise on Data.World.
2. Import the data from the table at https://en.wikipedia.org/wiki/List_of_Academy_Awards_ceremonies#Ceremonies
2. Create a measure that averages the [Sum] column and converts that value to an integer.
3. Merge the two queries together based upon the Oscar Winners [ceremony_number] column and the Ceremonies [#] column. Keep only the Date from the Ceremonies query in your final query.
4. Use Power Query to calculate the age of the person at the time they won the award. (Hint.)
5. Use Power Query to create a column that contains only the year of the award ceremony.
6. Create a scatterplot that shows the person’s age on the y-axis and the year of the ceremony on the x-axis. There should be one dot per award per year.
7. Ensure the tooltip on the scatterplot shows the following: Award (best actor, actress, or director), ceremony year, age, person’s name, movie.
8. Use the new Button Slicer visual (available in Nov 2023 release of PBI Desktop) to create a slicer for users to choose which awards to show in the scatterplot. The button slicer should meet the following requirements:
- Place a color image to the left of the text in each button.
- Add a count of people in each button, underneath the award type.
- Format the button so that the image is blurred when you hover over it
- Format the button so that the button has a bright gold border when it is selected.
- Format the button so that images in unselected buttons are desaturated/gray and any selected buttons contain the full color image.
Dataset
This week’s data has two sources.
- Data.World: https://data.world/mlongoria/oscarwinsbyceremony. You will need to log in to access the data, but accounts are free.
- Wikipedia: https://en.wikipedia.org/wiki/List_of_Academy_Awards_ceremonies#Ceremonies
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. 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.
Solution
Solution File available for download via Data Stories Gallery.
hello
‘Create a measure that averages the [Sum] column and converts that value to an integer.’
first task very confusing average of what ??
In the dataset there is a column named “Sum”. When something is in brackets like [Sum], it refers to a field name in Power BI. It’s not the best name for a field/column, but we didn’t make the names in this dataset.
hi! A direct import from web for the ceremonies table yields some funkiness with tables. There are HTML Tables and Suggested tables. In HTML, the cloests table we nee is Ceremonies[edit] but that is missing some columns. Table 6 in Suggested Tables has all the columns, and then some, but the merged cells and line break within cells are messing things up. Am I the only one seeing this?
Hi, Chris. You actually only need 2 columns from the table (# and Date). The Ceremonies[edit] table will work just fine. You can check out the solution file in the Data Stories gallery (linked on this post under the Solution heading) for more info.
I can’t find the [SUM] column in the dataset i’m i missing something?
Hi Mohamed – great question. The sum is calculated using a measure in Power BI. https://dax.guide/sum/