Understanding the difference between VALUES(), DISTINCT(), ALL() and ALLNOBLANKROW()
Table of Contents
Introduction
When working with DAX in Microsoft Power BI, functions like VALUES(), DISTINCT(), ALL() and ALLNOBLANKROW() may seem similar because they are often used to retrieve unique values from a column. However, they differ in how they handle filters and blank rows, which becomes important when working with datasets such as the Adventure Works Sample Database.
If you’re new to DAX or want to strengthen your fundamentals before moving into advanced patterns, this concept is also covered in depth in our Power BI DAX training resources, where real-world examples make these differences easier to understand.
To make these differences clearer, this blog uses a small and simplified data model with just two tables: Products and Sales. By intentionally introducing invalid Product IDs in the Sales table, we can clearly observe how each DAX function behaves with respect to the incoming filter context, blank rows.
By the end of this blog, you will have a solid understanding of when to use each function and how blanks are handled. You will also learn why results may differ even when the functions seem similar. This knowledge will help you write more accurate and reliable DAX measures in Power BI.
Why a Simplified Dataset Is Used
This confusion becomes even greater when you practice on large datasets such as the Adventure Works Sample Database. To make the differences easier to understand, we will use a simple dataset with only a few rows.
Before analyzing how these DAX functions work, let’s first review a simple example dataset.
Dataset Overview
The data set consists of two tables, connected by the Product ID column. The Product table contains 4 products (cellphones).
The Sales table contains the quantity of different products sold. Notice that some Product IDs (5 and 6) exist in the Sales table, but these products do not exist in the Product table.
Creating Measures Using Different DAX Functions
To understand the behaviour of different DAX functions for the same column in the data set:
- Right click on the Product table.
Since the data set is very small and this blog is purely focused on understanding the DAX functions, we will not be creating a separate table for measures.
2. Click on New Measure.
Measure tools open.
3. Type the following DAX expression in the formula bar and press Enter.
In this expression:
- The DISTINCT() function returns the distinct products in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Right click on the Product table.
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the Formula bar and press Enter.
In this expression:
- The VALUES() function returns the distinct products in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Right-clickon the Product table.
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the formulabar and press Enter.
In this expression:
- The ALL() function removes any filters from the Product table and returns all rows in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Right-clickon the Products table.
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the formulabar and press Enter.
In this expression:
- ALLNOBLANKROW() removes filters and excludes the blank relationship row.
- COUNTROWS() counts the rows in the virtual table.
Visualizing the Results in a Matrix
- Click on the matrix visual in the visualization pane.
- Drag and drop Product Name from the Product table to the rows.
- Drag and drop Count Rows using DISTINCT (CM) from the Product table to the Values.
- Drag and drop Count Rows using VALUES (CM) from the Product table to the Values.
- Drag and drop Count Rows using ALL (CM) from the Product table to the Values.
- Drag and drop Count Rows using ALLNONBLANK (CM) from the Product table to the Values.
Analysing the Results
Now that you have used all the different functions in separate measures, lets analyse the results.
- The blank row in the Product Name indicates the invalid relationship created by the Product ID 5 and 6.
- Count Rows using DISTINCT (CM) counts the Distinct products and shows the results based on the incoming filter context.
- Another important consideration at this point is that DISTINCT() does not count the blank value and the total distinct products is 4.
- Count Rows using VALUES (CM) counts the Distinct products and shows the results based on the incoming filter context.
- VALUES() count the blank value, and the total distinct products is 5.
- Count Rows using ALL (CM) counts the Distinct products and shows the results ignoring the incoming filter context.
- ALL() counts the blank value, and the total distinct products is 5 (Which is same for all rows and totals).
- Count Rows using ALLNONBLANKROW (CM) counts the Distinct products and shows the results ignoring the incoming filter context.
- ALLNONBLANK() does not count the blank value and the total distinct products is 4 (Which is same for all rows and totals).
Conclusion
Although VALUES(), DISTINCT(), ALL(), and ALLNOBLANKROW() may look interchangeable at first glance, they serve different analytical purposes in DAX.
- DISTINCT() respects filter context and returns unique values from a column. It can include blanks if they exist in the data but does not create the additional blank row used to represent invalid relationships.
- VALUES() also respects filter context but includes blank rows, which often represent invalid or missing relationships.
- ALL() removes filters from a table or column and returns all rows, ignoring the current filter context. Because filters are removed, measures using ALL() often return the same value across rows and totals.
- ALLNOBLANKROW() removes filters like ALL(), but it excludes the special blank row that represents invalid relationships.
Understanding these subtle but critical differences helps you write more accurate measures, debug relationship issues, and avoid misleading totals in Power BI reports. Mastering these functions is a key step toward becoming confident with DAX.
FAQs
Both return unique values, but VALUES() includes blank rows, while DISTINCT() excludes blanks. This difference becomes important when your data model has invalid or missing relationships.
The blank row represents rows in the fact table that do not have a matching value in the dimension table, often due to invalid or missing keys in the relationship.
Use ALL() when you want to ignore all filters on a column or table, commonly used in percentage calculations, benchmarks, and grand totals.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enroll in
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI Service Course
Here, you can access detailed courses and resources designed to enhance your Power BI skills and take your reporting capabilities to the next level.
