While preparing for the Microsoft Power BI Data Analyst exam, I came across the 25 Days of DAX Fridays! Challenge from Curbal, a popular Power BI learning resource platform.

There have been two editions of the challenge so far. I spent some time last week completing the first edition, which uses a dataset on a fictional company called NorthWind. There are 25 questions in total that need to be answered using DAX. In this post I’ll go through how I solved the questions for days 1 to 5.

Data model

Before we dive into the questions, let’s examine the data model for the NorthWind dataset.

Data model for NorthWind dataset from 25 Days of DAX Fridays! Challenge edition 1

There are seven tables in the model (I have also created a _Measures table to house all of my custom measures). I have arranged the dimension tables at the top and the Orders fact table at the bottom, making it clear how the filters propagate from the dimension tables to Orders. All relationships are one-to-many, and the cross filter direction is always single so that filters propagate only from the one-side to the many-side.

  1. Calendar (dimension table)
    This has been marked as a date table in the model, with Calendar[Date] as the primary key. There are three relationships with the other tables:

    • Calendar[Date] and Orders[OrderDate] (active relationship, indicated by solid line above)
    • Calendar[Date] and Orders[ShippedDate] (inactive relationship, indicated by dashed line above)
    • Calendar[Date] and Employees[HireDate] (inactive relationship, indicated by dashed line above)
  2. Employees (dimension table)
    • Primary key is Employees[EmployeeID]
    • Employees[EmployeeID] is related to Orders[EmployeeID]
  3. Customers (dimension table)
    • Primary key is Customer[CustomerID]
    • Customer[CustomerID] is related to Orders[CustomerID]
  4. Categories (dimension table)
    • Primary key is Categories[CategoryID]
    • Categories[CategoryID] is related to Products[CategoryID]
  5. Suppliers (dimension table)
    • Primary key is Suppliers[SupplierID]
    • Suppliers[SupplierID] is related to Products[SupplierID]
  6. Products (dimension table)
    • Primary key is Products[ProductID]
    • Products[ProductID] is related to Orders[ProductID]
  7. Orders (fact table)
    • Beware that Orders[OrderID] is not a primary key of the Orders table. In fact, the grain of the Orders table is each unique combination of Orders[OrderID] and Orders[ProductID], i.e. each product sold as part of an order. It is very important to keep this in mind for some of the questions related to orders.

There are 2155 rows in the Orders table but only 830 distinct values in the Orders[OrderID] column.

DAX solutions

I’ll explain how I used DAX to derive each day’s answer below. To see the expected answers, go to the Advent Calendar on the challenge page and click on the card for the respective day.

Note that it is usually possible to get to the answer by dropping relevant fields into a table or matrix visual and reading off a particular cell where the desired information is summarised. However, the challenge requires writing a measure for each day’s question that would result in the expected answer.

Day 1: How many current products cost less than $20?

To consider current products only, we need to filter on the Products[Discontinued] column where the value is False. (Here we have used an equivalent expression wrapping the column name in NOT.) We also filter on Products[UnitPrice] where it is less than 20 dollars. We apply both filters using CALCULATE and then count the number of rows in the Products table under this modified filter context.

Day 2: Which product is the most expensive?

First, we calculate HighestUnitPrice by finding the maximum value of the Products[UnitPrice] column. Using LOOKUPVALUE we can scan the entire Products[UnitPrice] column for this HighestUnitPrice value. The row in Products with the most expensive product is returned, and we simply retrieve the Products[ProductName] within that same row.

Day 3: What is the average unit price for our products?

This is a simple AVERAGE over the Products[UnitPrice] column.

However, in order to reuse this measure in the solution for day 4 below, we will wrap it all up in CALCULATE with REMOVEFILTERS() (same as ALL() but more readable) so that all existing filters are removed.

If we slice by Products[CategoryName], we see that the measure [03a. Average unit price for products (without REMOVEFILTERS)] results in a different average unit price per category, since on each row Products[CategoryName] is filtered and therefore part of the filter context. On the total row, however, Products[CategoryName] is not filtered, so the average unit price is calculated across all products. With the use of REMOVEFILTERS in the measure [03b. Average unit price for products], we clear all existing filters (including the one on Products[CategoryName]), so the same average unit price across all products appears on every row.

Average unit price without and with REMOVEFILTERS

Day 4: How many products are above the average unit price?

Incorrect solution

First, we will look at an incorrect solution. We can filter the Products table on Products[UnitPrice] where it is greater than the average unit price. We have already calculated the average unit price for all products back in day 3. Then we count up all the rows remaining in this filtered table.

Products above average unit price, calculated incorrectly and correctly

We see that for all categories a blank is returned for [04a. Number of products above average unit price (WRONG)], compared to the correct numbers for [04b. Number of products above average unit price (with row context)]. To understand why the blanks came to be, we need to remember that there is always an implicit CALCULATE wrapped around a measure when the measure is used.

In the measure above, FILTER iterates through the rows of the Products table, so a row context is created. In line 5 where the measure [03a. Average unit price for products (without REMOVEFILTERS)] is used, a concept in DAX called context transition occurs because of the implicit CALCULATE around the measure. This means the current row context (the row in the Products table containing the particular product we are iterating on) is converted to an equivalent filter context. Now, within this updated filter context, what is the value of [03a. Average unit price for products (without REMOVEFILTERS)]? In fact, it is the same as the unit price of the current product being iterated on, since the average is calculated on the one and only product within this modified filter context. Consequently, the result of Products[UnitPrice] > [03a. Average unit price for products (without REMOVEFILTERS)] is always False, resulting in a BLANK for every single product in the Products table and hence the incorrect solution above.

Correct solution

To compute the results correctly, all we need to do is use the measure [03b. Average unit price for products] instead, which uses the REMOVEFILTERS modifier internally as discussed in day 3.

This essentially nullifies the effects of context transition here and ensures that when iterating through the Products table we are always comparing against the average unit price across all products.

Update 2023-06-27:
The solution above is actually rather inefficient. When FILTER iterates over the Products table, the measure [03b. Average unit price for products] is evaluated once for every row – in this case 77 times, once for each row in Products. Since the average unit price across all products is always the same, we can extract it into a variable and refer to this variable within the FILTER iterator. A variable is only evaluated once within the same scope and helps speed up DAX evaluation time.

Products above average unit price, with improved measure 04c showing the same results as 04b

Day 5: How many products cost between $15 and $25 (inclusive)?

This is relatively easier than day 4. We need to filter on Products[UnitPrice] and identify all rows where the value is both a) greater than or equal to 15 dollars and b) less than or equal to 25 dollars. Then we simply count up the number of rows that remain in the Products table under this modified filter context.


I hope the walkthroughs above have been useful in understanding how to approach writing a measure in DAX. It is always best to break down the problem into smaller parts and work through each part one by one. We’ll go through the solutions for days 6 to 10 next time:

Solving days 6 to 10 – 25 Days of DAX Fridays! Challenge edition 1

 

Vincent Ging Ho Yim
Author: Vincent Ging Ho Yim

Vincent has always enjoyed learning new things as well as finding elegant and efficient solutions to problems since childhood. He studied linguistics at university and has subsequently worked in theatre lighting and broadcast captioning. In his previous job he found his passion working with data and decided to pursue a change in career. In his spare time he likes reading, learning languages (both human and programming ones) and playing Pic-a-Pix and sudoku. He loves laksa, sushi and burritos.