In my third presentation, I utilized Data Analysis Expressions (DAX) to address the challenge of identifying out-of-stock products within a comprehensive general sales database. The challenge came 25 Days of DAX Fridays! Challenge – Ed1: NorthWind Company Question 16 ( DAX, developed by Microsoft, is a powerful formula language designed for data modeling and analysis in various platforms, including Excel Power Pivot, Power BI, and SQL Server Analysis Services. Once I had completed the challenge using DAX to formulate summary tables and measures, I presented a live demo to the office, explaining the steps I took to arrive at the final answer. Below is a brief description of those steps.

Import the data

This diagram illustrates how the data was mapped, showcasing the different relationships between various tables. As my focus was solely on out-of-stock products, I directed my attention to the Products table

Use Visualization to answer the Question

To obtain a preliminary understanding of the expected result, I generated a table and included the Product Name and UnitInStock columns. Subsequently, I applied a filter to the UnitInStock column, displaying only values equal to zero. This identified five products that were out of stock. The next step was to replicate this process using DAX and present the result on a single card

Summary Table

To begin with, we need to create a summary table to calculate the grand total value of the rows where the product stock level equals zero. We need to create a summary of the product table with the columns ‘product name’ and ‘stock level.’ We can create this table using DAX. It summarizes the product table and reduces it to its product name, then creates a column called ‘Stock’ that is just equal to the SUM of the ‘UnitInStock’ level. Now all we need to do is count the rows where the stock value is 0. (The SUMMARIZE function is pivotal for constructing summary tables. It helps in generating a table that includes aggregated values based on specified columns. This function is often used in conjunction with others to create more complex summaries).

Create Measure

We can achieve this by creating a measure in DAX. The COUNTROWS function is utilized to count rows, with the FILTER condition set to stock = 0. The summary table code is then copied into the measure. This counts the number of products where the stock is 0.


Next, we can simply drag the created measure into a card, perform some basic formatting, and the problem is solved.




Felix Ralphs
Author: Felix Ralphs