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

Power Query FILTER()

Rob Collie takes us through a good use of FILTER() in DAX: The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function.  In both examples here, I’ve highlighted the offending measure in yellow. CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6) CALCULATE([Sightings […]

Read More

Removing Text Between Delimiters in Power Query

Imke Feldmann has a new M function for us: While there is a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031