DISTINCT() vs VALUES()
DISTINCT() vs. VALUES() are two commonly used table functions in DAX that help calculate tables based on specific requirements. However, these functions do not retrieve existing tables from the data model; instead, they create new ones to enrich data by adding extra information. Moreover, table functions always return a table, which you can use directly for analysis or as a virtual table that acts as an iterator for other functions. In this blog post, we provide a brief description of DISTINCT() and VALUES(). Since both functions can sometimes be used interchangeably, we also explain their key differences at the end.
Using DISTINCT()
DISTINCT() and VALUES() may appear similar, yet they have slight differences.
To understand how DISTINCT() is used to create tables, follow these steps:
- Begin by clicking on the Modeling tab in the Ribbon.
- Next, click on New Table.
After that, enter the following expression in the formula bar and press Enter.
In this Expression:
The unique product categories from the Product table are retrieved because the argument to DISTINCT() is a table column.
- First, click on the Data tab in the left Ribbon.
- Then, select Testing Table Functions using DISTINCT (CT) in the Fields pane.
Using VALUES():
The result of VALUES() depends on the input argument:
Case #1: The input argument is a table:
If you input a table name into the VALUES() function, it instead returns all rows of the table while still keeping duplicates.
This is the example of above-mentioned scenario is:
In the above example, the new table, ‘Testing Table Functions using VALUES (CT),’ contains all rows from the Product table. For a more detailed explanation, refer to the complete step-by-step guide by visiting the link below.
Using VALUES() in Calculated Tables – Power BI Training Australia
Case #2: The input argument is a column:
When you input table columns into the VALUES() function, it automatically returns a single column containing only unique values, thereby removing all duplicates.
Example of the above-mentioned scenario is:
The expression above ultimately creates a table that contains only unique values for the product categories. As a result, the resulting table, ‘Testing Table Functions using VALUES (CT)’, excludes duplicates. For further clarification, you can follow a step-by-step guide on this example by visiting the link below.
Using VALUES() in Calculated Tables – Power BI Training Australia
DISTINCT() vs. VALUES():
Both of these functions return non-duplicating distinct values when a column is passed as an argument. However, DISTINCT() ignores BLANK() values, while VALUES() includes BLANK(). As a result, the choice between these functions depends on whether blank values are relevant to your analysis.
Conclusion:
Table functions undoubtedly play a crucial role in the data enrichment stage of a BI process. Specifically, these functions generate tables as output, but the results vary depending on the function used and the argument provided. Among them, DISTINCT() and VALUES() are two commonly used table functions that can produce similar results under certain conditions. Therefore, this blog post introduces these functions while also explaining their specific use cases.
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: