Today I took on the 25 days of DAX Fridays! Challenge by Curbal! Being relatively new to DAX (we only learnt it yesterday), I was a bit nervous about this challenge. But you know what they say, there is no way better to learn than just jumping straight into the deep end!
Our task for the Friday presentation was to work through as many daily challenges as possible, and then select the hardest one to present. Our group was given the Edition 1 challenges which used the NorthWind Company data. I managed to work up to Day 14, and found that to be a unique challenge for me. In this blog post I will take you through my processes, whilst doing my best to explain DAX along the way!
Setting up your table chart
The day 14 challenge wanted me to find out how many customers have never purchased “Queso Cabrales”. This can be broken down in to 3 main components:
- Company Name (from the Customers table)
- Total Sales (from the Orders table)
- Product Name (from the Products table)
These three components will make up your table chart. Having a table chart ensures you can visually check and see what is going on, and what manipulations you are making to the data.
One important thing to remember is to check the data in the tables. For example, in the Customers table, I needed to check that the Company Name has the exact same amount of distinct values as the table has rows. This just ensures that there is one CustomerID for each Company, and that there are no repeated values or missing data. This same process goes for Product Name in the Products table
Manipulating the table chart
From there, I want to find out which companies have/have not purchased the specific product “Queso Cabrales”. In the filters tab, click on Product Name, and select “Queso Cabrales”. This will then filter the table chart to only show companies that have purchased “Queso Cabrales”. However, since we want to find out with companies have NOT purchased the product, we need to click ‘Show items with no value’ on Company Name in the columns section.
We should now have blank values in the sales column, with the ‘blank’ representing no purchase transaction from a company.
DAX is an extremely powerful tool, and is very flexible, so there are numerous ways to achieve the same outcome. This is how I approached the code.
First, I wanted to calculate the total sales for each company that comes from the Queso product name:
CALCULATE([Total sales], FILTER(Products,Products[ProductName] = "Queso Cabrales"))
I found it helpful to actually write the code in a structure similar to how I would talk through the calculation. For instance, I would say something like “If the the sales amount is blank in the table, then that must mean there has been no purchase. So I must tally that to have a value 1 (since I want to count how many customers have not purchased Queso”.
Similarly, the code would look like this:
IF( ISBLANK( CALCULATE([Total sales], FILTER(Products,Products[ProductName] = "Queso Cabrales")) ) ,1,0)
Now at this point, bring a card onto your report view and drop the measure onto it. You’ll notice that it isn’t giving you a correct value. This is because this calculation isn’t actually tied to anything. We want to find out the result of this calculation for each company name.
Therefore, we need to use a SUMX function on the customers table, which just means the previous calculation we did will be applied to each row on the customers table (remember, each row is a Company Name/CustomerID).
SUMX(Customers, IF( ISBLANK( CALCULATE([Total sales], FILTER(Products,Products[ProductName] = "Queso Cabrales")) ) ,1,0) )
And there we have it – the result to the day 14 DAX challenge! I hope this provides a handy overview of not only the challenge, but how DAX works in terms of measures.