2024 Week 40 | Sigma: Can you avoid the food?

Introduction

Welcome to Week Forty! In my last article, Week 36, we covered how to affect multiple columns using the same filter. This week, we’re going to focus on even more advanced filtering techniques.

What do you do when you want to compare multiple arrays? Luckily Sigma recently released a brand new function: ArrayIntersection.

In this challenge, you will create a small dashboard that filters various food truck menu items based on ingredients that can be found in an array.

Good Luck!
-Ashley

Need access to Sigma?

Click here to Watch: How to Register & Get Started with Sigma WoW

Note: You will only have view access to WOW Workbooks, Folders, and Workspaces, not edit access. Please create your WOW Workbooks under “My Documents.” We suggest creating a folder to organize all your workbooks.

Requirements

On a hidden Data Tab:
- First, we want to create a table with our filter values:
  - Add the Tasty Bytes Ingredients List data model:
    - Workspaces / Workout Wednesday / 2024 / 2024W40 – Can you avoid the food? / Tasty Bytes Ingredients List
    - This table contains all ingredients from our main dataset, already in a single column
  - Create a new page control with this Ingredient column as its value source.
    - No targets should be applied at this stage.
    - Allow multiple selection
    - Do not show the null option
- Next, we want to bring our main dataset into the workbook
  - Add the Tasty Bytes Menu dataset in another table:
    - Workspaces / Workout Wednesday / 2024 / 2024W40 – Can you avoid the food? / Tasty Bytes Menu
    - Note all of the metrics available
    - Add a Summary calculation to find the Total Menu Items Count.
    - For clarity later, change the title of this table to state it is the “Parent”
- Now we want to create a table that will be the source of all our visualizations. We do this so that the Summary Value remains static, despite any filtering done by the user.
  - Create a child table from our Parent table
    - Add “For Elements” to its title. You will build all visible elements from this table.
    - Add a new column that compares the [ingredients] array and the [Ingredient] page control.
      - This column should return True if the [Ingredient] page control has no user-selected values OR if there is at least one ingredient found in both arrays.
On a visible page:
- Create a horizontal bar graph:
  - X-Axis: Truck Brand Name
  - Y-Axis: Menu Items Count metric
  - Color: Change the color to one of your choice
  - Add Data Labels
- Create a KPI Chart
  - Value: Menu Items Count
  - Comparison: Lookup the Total Menu Items Count from the Filter Parent Table
    - Change this to display the “% of” with a meaningful label
  - Center align this element
- Create a vertical bar graph:
  - No X-Axis
  - Y-Axis:
    - Dairy Free Items
    - Gluten Free Items
    - Nut Free Items
    - Healthy Items
  - Add data labels
  - Change colors if you wish
- Add checkbox controls for each of the Dietary Restriction columns (Is Dairy Free, Is Gluten Free, Is Nut Free, Is Healthy):
  - Each should be True/All
  - Each should target the For Elements hidden data table
Bonus:
- The solution up to here allows users to show data that includes the specified Ingredient. In this case, it might make sense to allow users to dynamically choose whether they want to include or exclude the Ingredient.
- Add a new Switch Page Control called [Include-Ingredient]:
  - Label True as Include
  - Label False as Exclude
  - Give this a description stating its purpose
- In the For Elements table, alter the existing Ingredients Filter column function. The function should now return True if any of these scenarios are true:
  - The [Ingredient] page control has no user-selected values
  - When the [Include-Ingredient] page control is True, there is at least one ingredient found in both arrays
  - When the [Include-Ingredient] page control is False, there are no ingredients found in both arrays
Finishing Touches
- - Give the Page a Descriptive Title
  - Arrange your elements into a cohesive dashboard. Don’t forget to move the Ingredients page control to the visible page!
  - Because all bars show something different, be sure to not reuse colors across elements.

Dataset

Workspaces / Workout Wednesday / 2024 / 2024W40 – Can you avoid the food?

After you finish your workout, share on LinkedIn, Sigma’s Community page, (or Twitter) using the hashtags #WOW2024 and #SigmaComputing, and tag Ashley Bennett, Eric Heidbreder, Katrina Menne, and Michal Shaffer!

Create an interactive, sharable version of your solution here.

Also, make sure to fill out the Submission Tracker so that we can count you as a participant this week to track our participation throughout the year.

Solution

Coming soon….