Continuing our journey through the “25 Days of DAX Fridays!” Challenge by Curbal, I invite you to explore the solutions I’ve crafted for the next set of challenges, spanning from day 11 to day 20. If you missed the earlier part where I tackled the questions from day 1 to day 10, you can catch up by referring to my previous blog post:

Cracking the DAX Code: Conquering Days 1-10 of the 25 Days of DAX Challenges

Now, let’s jump right into the next chapter of our DAX adventure and unlock even more insights together!
Day 11: How many customers have only ordered once?
In this solution, we first create a summary table that groups orders by CustomerID and calculates the distinct count of OrderIDs for each customer. Then, using the FILTER function, we count the number of customers whose distinct order count is equal to 1.

Day 12: How many new customers first purchased in current year?
Moving on to the 12th day, we’re interested in counting the number of customers who made their first purchase in the current year. This metric can provide insights into customer acquisition trends. Here’s the DAX solution:

Day 13: How many lost customers (have not purchased) in current year?
The CALCULATE function is used here to calculate the distinct count of CustomerIDs. The FILTER function narrows down the Orders table to include only those where the last order date is before the current year.

Day 14: How many customers have never purchased Queso Cabrales?
In this solution, we first create a calculated table QuesoCustomers that summarizes Orders by CustomerID for those orders where the product is Queso Cabrales. Then, using the EXCEPT function, we count the customers who are not present in the QuesoCustomers table.

Day 15: How many customers have purchased only Queso Cabrales (per OrderID)?
The solution involves nested filters. The first filter restricts the Orders table to only include orders for Queso Cabrales. The second filter involves a summarized table that counts the distinct number of products per OrderID. The final count is for customers where the distinct product count is less than 2, indicating they’ve purchased only Queso Cabrales.

Day 16: How many products are out of stock?
The CALCULATE function calculates the distinct count of ProductIDs where the UnitsInStock is equal to 0, indicating products that are out of stock.

Day 17: How many products need to restocked based on the restock level?
The solution uses the CALCULATE function to count the distinct number of ProductIDs. The FILTER function narrows down the Products table to include only those where the UnitsInStock is less than the specified Restock level.

Day 18: How many products on order we need to restock?
The solution is similar to the previous day’s solution. We again use the CALCULATE function to count the distinct number of ProductIDs. The FILTER function narrows down the Products table to include only those where the UnitsInStock is less than the Units In Order, indicating the

Day 19: What is the stock value of discontinued products?
In this solution, the CALCULATE function is used to calculate the stock value of discontinued products. The [Unit Price] * SUMX(Products, Products[UnitsInStock]) part calculates the value of stock for each product, considering its unit price and the total units in stock. The FILTER function narrows down the Products table to include only discontinued products.

Day 20: Which vendor has the highest stock value?
This solution utilizes the CALCULATE function to calculate the vendor with the highest stock value. The TOPN function is used to retrieve the top vendor based on the calculated stock value. The SUMMARIZE function summarizes the Products table by Vendor’s CompanyName and calculates the stock value for each vendor. The [stock value] column from the summary table is then used as a sorting parameter to determine the top vendor.

And there you have it! We’ve successfully tackled challenges from Day 11 to Day 20 of the “25 Days of DAX Fridays!” Challenge by Curbal. Stay tuned for the next installment of our journey as we continue to explore the exciting world of DAX and data analysis.

The Data School
Author: The Data School