Using COMBINEVALUES

Marco Russo explains why you might want to use the COMBINEVALUES operator in DAX:

When you import data in a Tabular model, relationships are optimized when they are based on a single column that does not have a high number of unique values. Columns that do have a high number of unique values are known as high cardinality columns. If two tables require two or more columns to define a relationship, it is possible to create a calculated column that concatenates the values of the columns used for the relationship, on both tables. This way, the relationship is based on one single column on each side. However, the resulting calculated column will have a higher cardinality than the original columns, which is not good for performance.

Most of the time, the presence of relationships based on multiple columns suggests that a better denormalization of the model is required to obtain an optimal star schema. Nevertheless, for smaller tables or when it is not possible to apply complex transformations, concatenating columns is the preferred way to obtain the relationships required. When this happens with data loaded in memory, the only concern is the cardinality of the resulting column. However, this is not the main concern in DirectQuery mode.

A calculated column in DirectQuery translates its expression into a native SQL expression. When this is used as a JOIN condition to express the relationship in a Tabular model, this could produce a non-optimal query plan, which could introduce unnecessary slowness in the query execution. For this reason, Microsoft introduced COMBINEVALUES, which is a function expressly designed to optimize relationships based on multiple columns in DirectQuery mode.

Read on for an example.

Related Posts

Joining Tables In DAX

Marco Russo bridges a gap in moving from SQL to DAX: The SQL language offers the following types of JOIN: INNER JOIN OUTER JOIN CROSS JOIN The result of a JOIN does not depends on the presence of a relationship in the data model. You can use any column of a table in a JOIN […]

Read More

Getting Month Names Given Numbers In DAX

Philip Seamark shows how to convert month numbers into names in Power BI: The most common solution I see offered is along the lines of a SWITCH statement that lists 12 conditions (one for each month).   This works, but can also be done using existing functions. While DAX lacks a dedicated function to convert a […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

May 2018
MTWTFSS
« Apr  
 123456
78910111213
14151617181920
21222324252627
28293031