My Solution to the '25 Days of DAX Fridays!' Challenge – Ed1 by CURBAL

13 Oct 2024

Introduction

Learning DAX can be scary and frustrating at times. Understanding some functions can take time. But with practice anything is possible, so is learning DAX. Unlike other programming languages, DAX doesn’t have that rich resources. This makes it ever harder to learn DAX. Luckily, there are some, though not enough, resources which provides us with a chance to practice DAX. One such resource is the “25 days of DAX Fridays!” Challenge by CURBAL. In this post, I will share my solution to the first edition of the challenge.

About the Challenge

My Solutions

Challenge 01:

How many current products cost less than $20?

CALCULATE(
  COUNT(Products[ProductID]),
  FILTER(
      Products,
      AND(Products[Unit Price] < 20, Products[Discontinued] = FALSE())
  )   -- Products[Unit Price] < 20 && Products[Discontinued] = FALSE() --> Same as AND()
)

Challenge 02:

Which product is the most expensive?

CALCULATE(
  VALUES(Products[ProductName]),
  FILTER(
      Products,
      Products[UnitPrice] = MAX(Products[UnitPrice])
  )
)

Challenge 03:

What is the average unit price for our products?
```
AVERAGE(
  Products[UnitPrice]
)
```

Challenge 04:

How many products are above the average unit price?

VAR
  avgPrice = AVERAGE(Orders[UnitPrice])
RETURN
  CALCULATE(
  COUNTA(Products[ProductID]),
  FILTER(
      Products,
      Products[UnitPrice] > avgPrice   -- Why can't I use the measure from the previous day (3) here?
  )
)

Challenge 05:

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

CALCULATE(
  COUNTA(Products[ProductID]),
  FILTER(
      Products,
      AND(Products[UnitPrice] >= 15, Products[UnitPrice] <= 25)
  )
)

Challenge 06:

What is the average number of products per order?

AVERAGEX(
  SUMMARIZE(
      Orders,
      Orders[OrderID],
      "ProductCount", COUNTA(Orders[ProductID])
  ),
  [ProductCount]
)

Challenge 07:

What is the order value in $ of open orders? (Not shipped yet)

CALCULATE(
  [Total sales],
  Orders[ShippedDate] = BLANK()
)

Challenge 08:

How many orders are “single item” (only one product ordered)?

COUNTROWS(
  FILTER(
      SUMMARIZE(
          Orders,
          Orders[OrderID],
          "TotalProducts", COUNTA(Orders[ProductID])
      ),
      [TotalProducts] = 1
  )
)

Challenge 09:

What is the average sales per transaction for “Romero y tomillo”?

CALCULATE(
  AVERAGEX(
      VALUES(Orders[OrderID]),
      [Total sales]
  ),
  FILTER(
      Customers,
      Customers[CompanyName] = "Romero y tomillo"
  )
)

Challenge 10:

How many days since “North/ South” last purchase?

DATEDIFF(
  CALCULATE(
      MAX(Orders[OrderDate]), // LASTDATE gives the same result.
      Customers[CompanyName] = "North/South"
  ),
  TODAY(),
  DAY
)

Challenge 11:

How many customers have ordered only once?

COUNTROWS(
  FILTER(
      // This is a multi-line comment.
      -- Okay?
      SUMMARIZE(
          Customers,
          Customers[CustomerID],
          "TotalOrders", DISTINCTCOUNT(Orders[OrderID])
      ),
      [TotalOrders] = 1
  )
)

Challenge 12:

How many new customers in the current year?

COUNTROWS(
  FILTER(
      // This is a single line comment.
      SUMMARIZE(
          Customers,
          Customers[CustomerID],
          "FirstOrder", FIRSTDATE(Orders[OrderDate])
      ),
      YEAR([FirstOrder]) = YEAR(TODAY())
  )
)

Challenge 13:

How many lost customers in the current year?

COUNTROWS(
  FILTER(
      SUMMARIZE(
          Customers,
          Customers[CustomerID],
          "LastOrder", LASTDATE(Orders[OrderDate])
      ),
      [Las Order] < DATE(YEAR(TODAY()), 1, 1) && [LastOrder] <> BLANK()
  )
)

Challenge 14:

How many customers have NEVER purchased Queso Cabrales

VAR AllCustomers = VALUES(Customers[CustomerID])
VAR QCCustomers =
  CALCULATETABLE(
      VALUES(Orders[CustomerID]),
      FILTER(
          ALL(Orders),
          Orders[ProductID] = 11
      )
  )
RETURN
  CALCULATE(
      DISTINCTCOUNT(Customers[CustomerID]),
      EXCEPT(AllCustomers, QCCustomers)
  )

  -- I didn't understand this formula properly? Is there any easier way?

… ```

Challenge 12:

How many new customers in 2022?
```
...
```

Challenge 13:

```
...
```

Challenge 14:

```
...
```

Challenge 15:

Blah Blah Blah
```
...
```

Challenge 16:

```
...
```

Challenge 17:

```
...
```

Challenge 18:

```
...
```

Challenge 19:

```
...
```

Challenge 20:

```
...
```

Challenge 21:

```
...
```

Challenge 22:

```
...
```

Challenge 23:

```
...
```

Challenge 24:

```
...
```

Challenge 25:

```
...
```