Table functions are a category of DAX functions used for calculating tables based on a certain requirement. These table do not exist originally as a part of data model and therefore, perform data enrichment by adding information on the top of data table. Table functions always produce a table as an output. This table can either be used directly for analysis or it can be used as a virtual table which acts an iterator for other functions. In this blog post, a brief description of the two commonly used table functions, DISTINCT() and VALUES() is presented. Since both of these can be used interchangeably at times so a difference between these two functions is also presented at the end.
Using DISTINCT()
DISTINCT() may look similar to VALUES() but they are slightly different.
To see how DISTINCT() is used to create tables:
- Click on the Modeling tab in the Ribbon.
- Click on New Table.
- Type the following expression in the formula bar and press Enter.
In this Expression:
- The unique product categories from the Product table are found because the argument to DISTINCT() is s table column
- Click on the Data tab in the left Ribbon.
- Click on Testing Table Functions using DISTINCT (CT) in the Fields pan.
Using VALUES():
The result of VALUES() depends on the input argument:
Case #1: The input argument is a table:
When a table name is used as input argument to the VALUES() function, then all the rows of the table are returned and the replicates are preserved.
An example of the above-mentioned scenario is:
In the above example, all the rows from the Product table are stored in the new table ‘Testing Table Functions using VALUES (CT)’. For a complete step by step guide on this visit:
Using VALUES() in Calculated Tables – Power BI Training Australia
Case #2: The input argument is a column:
When the table columns are used as input argument in the VALUES() function, then the output is a single column. This column contains only unique values, and all the duplicates are removed.
An example of the above-mentioned scenario is:
The expression above returns a table containing only unique values for the product categories. There are no duplicates in the resulting table ‘Testing Table Functions using VALUES (CT)’. For a step-by-step guide on the above-mentioned example, visit the link below:
Using VALUES() in Calculated Tables – Power BI Training Australia
DISTINCT() vs. VALUES():
Although both of these functions return non-duplicating distinct values when a column is passed as an argument, DISTINCT() does not count BLANK() as a value whereas, VALUES() returns BLANK().
Conclusion:
Table functions are frequently used functions in the data enrichment stage of a BI process. These functions produce tables as an output. The output however depends on the table function used and the argument provided to table function. DISTINCT() and VALUES() are the two commonly used table functions which can produce similar results in certain conditions. This blog post presents a brief introduction to these two functions along with the use case for each of these.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide the following Power BI trainings:
