2021 Week 18 | Power BI: DAX Trick – Cat or Croissant

Introduction

The Power BI Workout Wednesday team has decided that May is made for DAX! Each challenge this month will require some DAX in the solution. 

We are going to start our month of DAX with a fun game called Cat or Croissant. You might be familiar with the many versions of “animal or food” such as Corgi or Bread Loaf, Chihuahua or Muffin, Barn Owl or Apple, and Shrew or Kiwi. While these games are good, silly fun for humans, they are also used to train Artificial Intelligence (AI) algorithms to help them distinguish images that are similar in texture, form, and color. 

Our challenge this week won’t include any AI, but feel free to give Cognitive Services a try in Power BI Data Flows if you want extra credit. This exercise combines DAX with several visual elements. You’ll need to come up with a DAX formula that populates the current result of the game (in progress, won, lost) in addition to designing your own buttons for users to start/reset the game. There is no starter PBIX file this week – we are starting from scratch. There are no dependencies on any prior week. 

Requirements

  • Retrieve the data from data.world.
  • Add a column to the table (using M or DAX) that sets the sort order for the images such that the order changes each time the dataset is refreshed.
  • Present the images in a visual that allows users to select multiple images (hint: the example file uses a custom visual).
  • Create one or more DAX measures to determine if the game is in progress, won, or lost based upon the user’s selections. (hint: you’ll want to reference the IsCat column).
    •  The game is lost if any croissants have been selected. 
    • The game is won if all of the cats and none of the croissants have been selected
    • The game is in progress if no croissants have been selected and there are still cats left to be selected. 
  • Display the game status on the page in a visual with conditional formatting that changes the background or font color based upon the value. 
  • Use a bookmark to “reset” the game to be ready to play again with no images selected. 
  • Design your own images to act as buttons to allow the user start and reset the game. The example uses images made in PowerPoint, but you can use whatever program you would like. 
  • Include instructions to explain to the user how the game works. 
  • Colors, background, formatting, and number of pages are at your discretion.

Dataset

This challenge uses data provided on data.world specifically for this exercise.  You can use the data.world connector in Power BI (recommended) or download the Excel file to your local machine. You will need to log in to data.world to retrieve the data, but accounts are free. 

Special thanks to Matthew Roche, Lorna Brown, and Monica Rathbun for their contributions of kitty photos!

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. 

Solution

4 thoughts on “2021 Week 18 | Power BI: DAX Trick – Cat or Croissant”

  1. Hi Meagan. Thanks for the video! This is a fun challenge this week. Since you mentioned in the subtitle to select images as quickly as possible, I thought about a stopwatch feature will be really cool to add (to actually count the time you use to click all the right images). I did some research but it doesn’t seem to be easy. I found some solutions using PowerApps but it’s not available as a custom visual inside power bi desktop. I wonder if you know anything about PowerApps and if there’s a pre-requisite to use it in power bi that I’m not aware of.

    1. Also I don’t know if the intention is to use “reset” button and the order of the images will change too? This is not included in the video. (only manually click “refresh” work)

      1. Meagan Longoria

        Changing the order of the images requires a refresh of the dataset with the way that I built it, which can’t be done by a simple button. You could perhaps find a different way to sort the visuals using a measure that changes based upon another slicer, maybe to select different rounds. I haven’t tested this, but I think it would be cool to try out the new Power Automate visual to have it refresh the dataset, which would change the order of the visuals (I didn’t persue this since we couldn’t assume that everyone has PowerBI.com accounts and access to Power Automate).

        Great questions! I like the way you were thinking about it.

    2. Meagan Longoria

      We discussed trying to add a timer/stopwatch feature, but also found that it is fairly difficult. You could add one via the HTML content custom visual, but it would not affect or be affected by the selections in the game – it would just be an additional item on the page. I also thought about a PowerApp as an alternative, but we couldn’t assume that everyone had PowerApps available, so I didn’t pursue it. The PowerApps visual should be available in the latest version of Power BI Desktop. You can read more about it at https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/powerapps-custom-visual.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top