After last week’s cat fun from Meagan, you might expect an extended challenge like “Can you make dancing cat gifs in Power BI?” or something. That would be too predictable. While there is a theme in May, it’s related to using DAX, not cats.
This week’s challenge is to use DAX to create a measure that highlights the minimum and maximum values in a line chart. There are different ways to approach this problem, but don’t be surprised if you end up using DAX functions like MAXX, MINX, SWITCH, and more. The best part is that once you learn this technique, you can use DAX to highlight a variety of other things you may want to call out in your own visuals.
- Create an explicit DAX measure for Sales that sums the Financials[ Sales] column
- Create a DAX measure that uses variables to calculate the MIN and separately the MAX Sales value on any given Date, and then conditionally return a value only if the current Sales value equals a min or max. Apologies if that’s a confusing way of stating it, but look at the sample report embedded above to see how it’s visualized. This measure acts as your “highlight” measure.
- Add your Sales measure and your Highlight measure to a line chart (HINT: use the secondary axis)
- Use Small Multiples on the line chart
- This is a preview feature prior to the May Power BI Desktop release and would need to be enabled manually in Options and Settings–>Options–>Preview features
- If you’ve installed May’s update, you no longer need to manually enable small multiples.
- Set appropriate format options for markers, data labels, etc.
- Set currency formatting
- Extra Credit: Add a separate Date table and relate to the Financials table on Financials[Date]
This week uses sample Financials dataset available in Power BI Desktop.
The Financials dataset arrived in the October 2020 version of Power BI Desktop, and you can load it from the option on the report canvas or in Help–>Examples–>Sample dataset.
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.