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

[technolog]

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

 

It is built on the basis of tables

 

and the spreadsheets

 

Let's make a summary table based on these data

 

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

 

The next function is ALLNOBLANKROW.

 

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

 

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

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.

 

Let's display our tables on the dashboard

 

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

 

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