Horizontal Fusion
Horizontal Fusion is an optimization performed in a DAX query plan so that multiple SE requests that differs only for the filters applied to one or more columns are merged into a single SE request. It works for both DirectQuery and VertiPaq. This page include link to resources describing the optimization technique and details about cases that are not supported.
External resources
Introduction to Horizontal Fusion is a general introduction to Horizontal Fusion.
Optimizing fusion optimization for DAX measures describes how to get a query plan that is better than Horizontal Fusion in a specific scenarios.
Unsopported cases
Horizontal Fusion does not recognize scenarios where it could be applied, but the engine is not able to do that. For each scenario we provide possible workarounds. These cases could be supported in the future: in that case, this page should be updated.
Multiple selection of static slices on columns without groupby
The filter on a column has more than one element selected, and the column filtered is not part of a groupby column (in SUMMARIZECOLUMNS or similar functions).
// The following DAX query generates three xmSQL queries
// instead of one
DEFINE
MEASURE Sales[Sales Contoso] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] IN { "Contoso", "Adventure Works" } )
)
MEASURE Sales[Sales Fabrikam] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] IN { "Fabrikam" } )
)
MEASURE Sales[Sales Litware] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] IN { "Fabrikam", "Adventure Works" } )
)
MEASURE Sales[Test] = [Sales Contoso] + [Sales Fabrikam] + [Sales Litware]
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Color],
TREATAS ( { 2019 }, 'Date'[Year] ),
"Test", [Test]
)
Workaround #1
At the query level, add ‘Product’[Brand] to the calculation, so fusion takes place. It can be used with GROUPBY only for cumulative measures. It is not an option for measures because the query is generated by the client (e.g. Power BI).
EVALUATE
GROUPBY (
SUMMARIZECOLUMNS (
'Product'[Color],
'Product'[Brand],
TREATAS ( { 2019 }, 'Date'[Year] ),
"Test", [Test]
),
'Product'[Color],
"Test", SUMX ( CURRENTGROUP(), [Test] )
)
Workaround #2
At the measure level, use Phil Seamark’s 1-Column fusion optimization to iterate all ‘Product’[Brand] values in the calculation, so fusion takes place. Side-effects: the materialization could be too large for high cardinality columns.
DEFINE
MEASURE Sales[Sales Contoso] =
SUMX (
KEEPFILTERS ( ALL ( 'Product'[Brand] ) ),
[Sales Amount] * ('Product'[Brand] IN { "Contoso", "Adventure Works" } )
)
MEASURE Sales[Sales Fabrikam] =
SUMX (
KEEPFILTERS ( ALL ( 'Product'[Brand] ) ),
[Sales Amount] * ( 'Product'[Brand] IN { "Fabrikam" } )
)
MEASURE Sales[Sales Litware] =
SUMX (
KEEPFILTERS ( ALL ( 'Product'[Brand] ) ),
[Sales Amount] * ( 'Product'[Brand] IN { "Fabrikam", "Adventure Works" } )
)
MEASURE Sales[Test] = [Sales Contoso] + [Sales Fabrikam] + [Sales Litware]
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Color],
TREATAS ( { 2019 }, 'Date'[Year] ),
"Test", [Test]
)
Table applied to filter context
Every time a table is applied to a filter context, fusion is not applied. This affects a number of cases:
- Time Intelligence functions (like DATESYTD)
- Predicates with range expressions (greater then, less than, …)
- Use of TREATAS
The following DAX query generates three xmSQL queries instead of one.
DEFINE
MEASURE Sales[Sales Contoso] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Contoso" ),
DATESYTD ( 'Date'[Date] )
)
MEASURE Sales[Sales Fabrikam] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Fabrikam" ),
DATESYTD ( 'Date'[Date] )
)
MEASURE Sales[Sales Litware] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Litware" ),
DATESYTD ( 'Date'[Date] )
)
MEASURE Sales[Test] = [Sales Contoso] + [Sales Fabrikam] + [Sales Litware]
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Brand],
TREATAS ( { 2019 }, 'Date'[Year] ),
"Test", [Test]
)
Also a predicate instead of a table function produces the same result because of the dependency on the external variables.
MEASURE Sales[Sales Contoso] =
VAR _LasttDate = MAX ( 'Date'[Date] )
VAR _FirstDate = DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 1, 1 )
RETURN CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Contoso" ),
'Date'[Date] >= _FirstDate && 'Date'[Date] <= _LasttDate
)
MEASURE Sales[Sales Fabrikam] =
VAR _LasttDate = MAX ( 'Date'[Date] )
VAR _FirstDate = DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 1, 1 )
RETURN CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Fabrikam" ),
'Date'[Date] >= _FirstDate && 'Date'[Date] <= _LasttDate
)
MEASURE Sales[Sales Litware] =
VAR _LasttDate = MAX ( 'Date'[Date] )
VAR _FirstDate = DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 1, 1 )
RETURN CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Litware" ),
'Date'[Date] >= _FirstDate && 'Date'[Date] <= _LasttDate
)
If the range is defined as a constant range of values that does not require a runtime computation during the query execution, the Horizontal Fusion is still applied. The following code produces a single xmSQL query, but it is not a generic workaround as it is a static filter and not a dynamic one (it cannot depend on model’s data).
MEASURE Sales[Sales Contoso] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Contoso" ),
'Date'[Date] >= dt"2019-01-01" && 'Date'[Date] <= dt"2019-12-31"
)
MEASURE Sales[Sales Fabrikam] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Fabrikam" ),
'Date'[Date] >= dt"2019-01-01" && 'Date'[Date] <= dt"2019-12-31"
)
MEASURE Sales[Sales Litware] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Litware" ),
'Date'[Date] >= dt"2019-01-01" && 'Date'[Date] <= dt"2019-12-31"
)
Workaround #1
The only current workaround is to use simple measures and apply the table filter (in this case DATESYTD) only at the higher level (Sales[Test] in this example).
MEASURE Sales[Sales Contoso] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Contoso" )
)
MEASURE Sales[Sales Fabrikam] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Fabrikam" )
)
MEASURE Sales[Sales Litware] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Litware" )
)
MEASURE Sales[Test] =
CALCULATE (
[Sales Contoso] + [Sales Fabrikam] + [Sales Litware],
DATESYTD ( 'Date'[Date] )
)
Dynamic filter applied to a column
If the filter applied to a column depends on a dynamic calculation the horizontal fusion optimization is not applied even if the filter is identical. Just the fact that it’s dynamically computed is enough to skip the possible fusion.
MEASURE Sales[Sales Contoso] =
VAR _LastDate = [DateLast]
RETURN
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Contoso" ),
'Date'[Date] = _LastDate
)
MEASURE Sales[Sales Fabrikam] =
VAR _LastDate = [DateLast]
RETURN
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Fabrikam" ),
'Date'[Date] = _LastDate
)
MEASURE Sales[Sales Litware] =
VAR _LastDate = [DateLast]
RETURN
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Litware" ),
'Date'[Date] = _LastDate
)
Workaround #1
The only possible workaround is to move the filter to the measure that consumes the simpler measures (without a filter).
MEASURE Sales[Sales Contoso] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Contoso" )
)
MEASURE Sales[Sales Fabrikam] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Fabrikam" )
)
MEASURE Sales[Sales Litware] =
CALCULATE (
[Sales Amount],
KEEPFILTERS ( 'Product'[Brand] = "Litware" )
)
MEASURE Sales[Test] =
VAR _LastDate = [DateLast]
RETURN
CALCULATE (
[Sales Contoso] + [Sales Fabrikam] + [Sales Litware],
'Date'[Date] = _LastDate
)