Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now


Comparing ALL/ALLNOBLANKROW with DISTINCT/VALUES: A Data Function Analysis

In today's world of data processing, where tables and spreadsheets are the primary tools for analyzing and presenting information, understanding the various functions and their application is crucial. In this article, we will review the basic concepts and techniques for working with data using the source data model. We will explore how to create a summary table based on this data, and apply a number of functions to the Department column, clarifying the parameters of these functions. We will then take a detailed look at the ALLNOBLANKROW, VALUES, and DISTINCT functions, and analyze the differences between the ALL/ALLNOBLANKROW and DISTINCT/VALUES function groups. Finally, we will demonstrate the results of these functions through a practical example by creating a filter for the "Departments" column and presenting our tables in a dashboard. This study will help readers gain a deeper understanding of how different functions affect data processing and how to effectively use them to analyze and present information.

Let's consider the source model of the data

Screenshot 2023-12-16 at 06.11.36.png

It is built on the basis of tables

Screenshot 2023-12-16 at 06.12.06.png

and the spreadsheets

Screenshot 2023-12-16 at 06.12.25.png

Let's make a summary table based on these data

Screenshot 2023-12-16 at 06.12.42.png

Let's apply a number of functions to the Department column by specifying the Department column in the function parameters

Screenshot 2023-12-16 at 06.13.00.png

The next function is ALLNOBLANKROW.

Screenshot 2023-12-16 at 06.13.21.png

VALUES function - creates a table with one column containing unique values from the source column or table (given an empty value)

Screenshot 2023-12-16 at 06.13.41.png

DISTINCT function - creates a table from one column with unique values of another table's column (not considering empty values).

Screenshot 2023-12-16 at 06.14.01.png

We have an Orders table that lists all the orders for a manager and a reference table that lists the managers and their departments.

Let's analyze the difference between the ALL/ALLOBLANKROW group and DISTINCT/VALUES.

ALL and ALLNOBLANKROW clear columns from the imposed filters, while DISTINCT and VALUES do not clear and take into account the previously imposed filters.

Let's check the work of the functions by creating a filter on the Departments column.

An empty department appeared in the filter, because we created a filter based on the Departments column from the Departments directory table linked to the Orders fact table, where a new fact was added with a manager not described in the directory.

Screenshot 2023-12-16 at 06.14.33.png

Let's display our tables on the dashboard

Screenshot 2023-12-16 at 06.15.02.png

Next, let's select two departments in our filter and look at the results returned.

The ALL function removes all filters and takes into account the empty line

The ALLNOBLANKROW function removes all filters without taking empty rows into account

The DISTINCT function is subject to filtering and does not consider empty strings

The VALUES function takes into account all filters and empty strings

Screenshot 2023-12-16 at 06.15.24.png

Screenshot 2023-12-16 at 06.15.35.png

 From the screenshots you can see all the differences in the operation of these functions.