Last week, we were assigned to tackle the 25 Days of DAX Challenge. This challenge has been an incredible opportunity for me to get my hands dirty with DAX. I realized there were multiple ways to approach one problem with DAX. Let me walk you through my journey. I will be taking example of challenge Day 20, and the challenge question was: “Which vendor has the highest stock value?” It seemed straightforward, but as I dove into it, you realize the constraints and flexibility of DAX.
My Initial Solution:
First, as always I calculate my base calculations which in this case is the stock value for each product. I used the SUMX function to do this. Here’s what it looked like:
StockValue = SUMX(Products, [Unit Price]*[UnitsInStock])
With the stock value calculated, I then had to find the supplier with the highest stock value. I used the TOPN function for this:
TopSupplierID =
CALCULATE(
TOPN(1, VALUES(‘Products'[SupplierID]), [StockValue]),
ALL(‘Suppliers’)
)
Finally, I fetched the company name associated with the top supplier ID from the Suppliers table using the SELECTEDVALUE and FILTER functions:
CompanyName =
CALCULATE(
SELECTEDVALUE(Suppliers[CompanyName]),
FILTER(‘Suppliers’, [TopSupplierID]== Suppliers[SupplierID])
)
My Final Solution:
As I continued to explore, I realized there were other ways to solve this problem. One approach involved using variables to make the calculation process clearer and shorter than my first solution:
TopSupplierName =
VAR TopSupplierID =
TOPN(1,
VALUES(‘Products'[SupplierID]),
[StockValue]
)
RETURN
CALCULATE(
VALUES(‘Suppliers'[CompanyName]),
‘Suppliers'[SupplierID] = TopSupplierID
)
So, as you can see, first of all I created the variable named TopSupplierID and used TOPN there to find the supplier ID with highest stock value. Now, I needed actual supplier name instead of supplier ID so, using CALCULATE function in return, we will get the vendors name. Comparing it to my first solution, it was way shorter and faster to get to the same result so, I’m sticking with using and exploring variables more.
Alternative Approaches:
The next method I discovered was using the SUMMARIZE function, which was new to me. So, this function will summarize the data based on grouping the columns you feed in. The actual solution which you’ll find for this challenge utilizes SUMMARIZE function along with TOPN function to find the vendor with highest stock. So, have a look here if you’re interested.
That’s all for this blog. Happy DAX-ing, everyone!
#dax #powerbi #dataanalysis