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.

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.

**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)

**Employees (dimension table)**- Primary key is
*Employees[EmployeeID]* *Employees[EmployeeID]*is related to*Orders[EmployeeID]*

- Primary key is
**Customers (dimension table)**- Primary key is
*Customer[CustomerID]* *Customer[CustomerID]*is related to*Orders[CustomerID]*

- Primary key is
**Categories (dimension table)**- Primary key is
*Categories[CategoryID]* *Categories[CategoryID]*is related to*Products[CategoryID]*

- Primary key is
**Suppliers (dimension table)**- Primary key is
*Suppliers[SupplierID]* *Suppliers[SupplierID]*is related to*Products[SupplierID]*

- Primary key is
**Products (dimension table)**- Primary key is
*Products[ProductID]* *Products[ProductID]*is related to*Orders[ProductID]*

- Primary key is
**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**, i.e.*Orders[OrderID]*and*Orders[ProductID]***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.

- Beware that

## 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.

### 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.

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 theProductstable, the measure`[03b. Average unit price for products]`

is evaluated once for every row – in this case 77 times, once for each row inProducts. 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.

### 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: