Introduction

Continuing on from last weeks blog on context in Power BI, this week our presentation was to choose one of the 25 days of DAX challenges and present our solution.  In this blog I will go through the solutions for three of my favorite DAX challenges.  These will be the one I presented, Day 23, Day 17, and Day 9.  With all DAX problems, there are many different solutions, these are just how I approached them.  Hopefully these can help you out if you get stuck or even offer a solution you didn’t think of.

 

Day 23

Starting with my presentation choice, day 23.  I stuck with the data provided in the initial download so I could not get the answer for 2022 as requested on the website.  I changed the goalposts though and made the target to find out the employee with the most sales for 2020.  To do this created a bar chart with the employees name on one axis, total sales on the other and the year filtered to 2020.

 

This will show us that Margaret Peacock has the most sales for 2020.  Now we need to create a measure using DAX that will display just that name on a card.  We will start with a calculate function and do the following:

 

CALCULATE(SELECTEDVALUE(Employees[Full Name]),

 

This starts the measure by saying we will need to calculate a selected value which is the employees full name.  Next we will need to add some conditions.  As we only need one name we will follow the comma with a Top N function:

 

CALCULATE(SELECTEDVALUE(Employees[Full Name]), TOPN(1, Employees,

 

At this stage, we have declared that we want the value of the employees full names and only the top 1.  After the last comma in the formula above we need to provide the criteria for what makes up the top employees.  This will require another calculate function and we will use the already established measure of ‘Total Sales’.  This measure provides the sum of sales.  The formula will now look like this:

 

CALCULATE(SELECTEDVALUE(Employees[Full Name]), TOPN(1, Employees, CALCULATE([Total sales],

 

Now we should have the Employees name that has the most sales overall.  This includes all years in the database so we will need to filter the second calculate function.  After the last comma we will add a filter to select only the year 2020.  Following that the Top N function will require a way to sort the data and that will be descending.  The complete formula will look like this:

 

CALCULATE(SELECTEDVALUE(Employees[Full Name]), TOPN(1, Employees, CALCULATE([Total sales], FILTER(‘Calendar’, ‘Calendar'[Year] = 2020)), DESC))

 

When we add this into a card visualization we will get the name of the employee with the most sales in 2020, Margaret Peacock:

 

Day 17

This was my initial choice to present however after completing Day 23 I changed my mind.  Day 17’s challenge requires the user to find how many items need restocking.  To do this we are to use the Restock Level and Stocked Units measures.  If a product has less Stocked Unites than its Restock Level then it needs to be restocked.  To get a rough idea of how this looks the table below shows product ID, Restock Level and Stocked Units.  As we can see, some of the values in the right column are less then the middle column, we need to get a count of those products.

 

 

The answer we are trying to achieve according to the website is 18 products.  This uses a logic which may be wrong and assumes products that have the same stock as a restock level do not need to be restocked.  As some restock levels are 0, this assumes that once a product sells out it does not get restocked.  However using that logic we can start our calculation.

 

To start, we want to calculate the distinct count of Product IDs, this will require the following code:

 

CALCULATE(DISTINCTCOUNT(Products[ProductID]),

 

We will need to add a condition to the second half of our calculate function.  The aim of this condition will be to filter the products we are counting to only products with a stock level that is lower than it’s restock level.  This is all that is needed for this challenge and the final code will be:

 

CALCULATE(DISTINCTCOUNT(Products[ProductID]), FILTER(Products, Products[UnitsInStock] <[Restock level]))

 

When we add this to a card we will see that 18 products need to be restocked which matches the require answer on the website.

 

 

 

Day 9

An early challenge that was surprisingly difficult.  This challenge requires you to find the average size per order.  In this case, size means amount of products.  Again, the logic here is a little bit flawed as it does not take into account quantity, the website example also has a dollar sign in front of the answer which confuses things even more.  The answer they are looking for though is that the average amount of product IDs in an order is 2.6.   We can get this value using quick measures and putting them in a table.  We can see below that there are 77 unique Order IDs and the average amount of products per Order ID is 2.6.

 

 

Now to get this using DAX it is a little bit more difficult.  We will need to group all rows by Order ID and then count how many distinct product IDs there are for each Order ID.  To iterate through rows in such a manner we will need to use a X function.  In this case we will use an AVERAGEX function.  The measure formula will start with this:

 

AVERAGEX(VALUES(Orders[OrderID]),

 

This will iterate through all the values specified which is Order ID and get the average of the value we provide after the comma.  In this case it will be the distinct count of product IDs.  The complete code will look like this:

 

AVERAGEX( VALUES(Orders[OrderID]), CALCULATE(DISTINCTCOUNT(Orders[ProductID])))

 

If we put this into a card we can see that we get the desired value of 2.6.  Both ways work however if you just want the specific value in a measure the above calculation is the way to go.

 

 

Conclusion

The 25 Days of DAX are a great way to practice your DAX skills and discover different ways to solve a problem.  As the title suggests, there are 22 more challanges available on their website which will be linked below.  I still have  along way to go with my DAX knowledge however I learnt a lot and really sharpened my skills by spending the day working through the challenges.  Thanks for reading my blog and good luck with your DAX endeavors!

 

25 Days of DAX Link: https://curbal.com/25-days-of-dax-fridays-challenge-ed1-northwind-company

Mikael Nuutinen
Author: Mikael Nuutinen