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.
Super User