How can I optimize this DAX query with SUMMARIZECOLUMNS
I’m working on a Power BI model (Premium capacity) with a large fact table (~131M rows).
I have a DAX query (used for a paginated report) that:
- Builds an “actual” dataset (A) from the fact table via SUMMARIZECOLUMNS
- Builds a “theoretical/expected” dataset (B) via another SUMMARIZECOLUMNS
- Adds several measures to each using ADDCOLUMNS
- Combines both datasets with a full-outer-join pattern using:
NATURALLEFTOUTERJOIN(A, B)
NATURALLEFTOUTERJOIN(B, A)
DISTINCT( UNION( JoinedFromA, JoinedFromB ) )
The query consistently fails in the Power BI Service with a Resource Governance / memory error.
I tried some optimization but still have the same issue :
EVALUATE
//------------------------------------
// DATASET A (View A)
//------------------------------------
VAR A_Base =
CALCULATETABLE (
SUMMARIZECOLUMNS (
'DimDate'[DateKey],
'DimDate'[Period],
'DimAttribute1'[CategoryA],
'DimAttribute2'[CategoryB],
'DimAttribute3'[CategoryC],
'DimEntityA'[EntityKey],
'DimEntityB'[ExternalKey],
'DimLocationA'[LocationCode],
'DimLocationA'[DirectionCode],
'DimGroupA'[GroupLabel],
'DimEntityA'[EntityName],
'DimAttribute1'[City],
'DimAttribute1'[PostalCode],
'DimStatus'[StatusLabel],
'DimHierarchyA'[HierarchyLevel],
'DimEntityA'[EntityType],
'DimEntityA'[EntitySubtype],
"MetricA_Main", [MetricA_Main]
),
'DimDate'[Period] = "2024-09",
'DimAttribute4'[CategoryA] = "All",
DimTypeOrder[OrderCode] IN { 11, 32}
)
VAR A_WithMetrics =
ADDCOLUMNS (
A_Base,
"MetricA_Flag1", [MetricA_Flag1],
"MetricA_Flag2", [MetricA_Flag2],
"MetricA_Flag3", [MetricA_Flag3],
"MetricA_Flag4", [MetricA_Flag4],
"MetricA_FlagAM", [MetricA_FlagAM],
"MetricA_FlagPM", [MetricA_FlagPM],
"MetricA_Status", [MetricA_Status],
"MetricA_Value1", [MetricA_Value1],
"MetricA_Value2", [MetricA_Value2],
"MetricA_Value3", [MetricA_Value3],
"MetricA_Value4", [MetricA_Value4]
)
VAR A_Filtered =
FILTER ( A_WithMetrics, NOT ISBLANK ( [MetricA_Main] ) )
VAR A_View =
SELECTCOLUMNS (
A_Filtered,
"Date", FORMAT ( 'DimDate'[DateKey], "DD-MM-YYYY" ),
"Period", 'DimDate'[Period],
"CategoryA", 'DimAttribute1'[CategoryA] & "",
"CategoryB", 'DimAttribute2'[CategoryB],
"CategoryC", 'DimAttribute3'[CategoryC],
"EntityKey", 'DimEntityA'[EntityKey] & "",
"ExternalKey", 'DimEntityB'[ExternalKey] & "",
"Location", 'DimLocationA'[LocationCode] & "",
"Direction", 'DimLocationA'[DirectionCode] & "",
"GroupLabel", 'DimGroupA'[GroupLabel] & "",
"EntityName", 'DimEntityA'[EntityName] & "",
"City", 'DimAttribute1'[City] & "",
"PostalCode", 'DimAttribute1'[PostalCode] & "",
"StatusLabel", 'DimStatus'[StatusLabel],
"Hierarchy", 'DimHierarchyA'[HierarchyLevel] & "",
"EntityType", 'DimEntityA'[EntityType] & "",
"EntitySubtype", 'DimEntityA'[EntitySubtype] & "",
"Metric_Value1", [MetricA_Value1],
"Metric_Value2", [MetricA_Value2],
"Metric_Value3", [MetricA_Value3],
"Metric_Value4", [MetricA_Value4],
"Flag1", [MetricA_Flag1],
"Flag2", [MetricA_Flag2],
"Flag3", [MetricA_Flag3],
"Flag4", [MetricA_Flag4],
"Flag_AM", [MetricA_FlagAM],
"Flag_PM", [MetricA_FlagPM],
"StatusFlag", [MetricA_Status]
)
//------------------------------------
// DATASET B (View B)
//------------------------------------
VAR B_Base =
CALCULATETABLE (
SUMMARIZECOLUMNS (
'DimDate'[DateKey],
'DimDate'[Period],
'DimAttribute4'[CategoryA],
'DimAttribute2'[CategoryB],
'DimAttribute3'[CategoryC],
'DimEntityC'[EntityKey],
'DimEntityD'[ExternalKey],
'DimLocationB'[LocationCode],
'DimLocationB'[DirectionCode],
'DimGroupB'[GroupLabel],
'DimEntityC'[EntityName],
'DimAttribute4'[City],
'DimAttribute4'[PostalCode],
'DimStatus'[StatusLabel],
'DimHierarchyB'[HierarchyLevel],
'DimEntityC'[EntityType],
'DimEntityC'[EntitySubtype],
"MetricB_Main", [MetricB_Main],
"MetricB_Alt", [MetricB_Alt]
),
'DimDate'[Period] = "2024-09",,
'DimAttribute4'[CategoryA] = "All"
)
VAR B_WithMetrics =
ADDCOLUMNS (
B_Base,
"MetricB_Flag1", [MetricB_Flag1],
"MetricB_Flag2", [MetricB_Flag2],
"MetricB_Flag3", [MetricB_Flag3],
"MetricB_Flag4", [MetricB_Flag4],
"MetricB_FlagAM", [MetricB_FlagAM],
"MetricB_FlagPM", [MetricB_FlagPM],
"MetricB_Status", [MetricB_Status],
"MetricB_Alt1", [MetricB_Alt1],
"MetricB_Alt2", [MetricB_Alt2]
)
VAR B_Filtered =
FILTER (
B_WithMetrics,
NOT ( ISBLANK ( [MetricB_Main] ) && ISBLANK ( [MetricB_Alt] ) )
)
VAR B_View =
SELECTCOLUMNS (
B_Filtered,
"Date", FORMAT ( 'DimDate'[DateKey], "DD-MM-YYYY" ),
"Period", 'DimDate'[Period],
"CategoryA", 'DimAttribute4'[CategoryA] & "",
"CategoryB", 'DimAttribute2'[CategoryB],
"CategoryC", 'DimAttribute3'[CategoryC],
"EntityKey", 'DimEntityC'[EntityKey] & "",
"ExternalKey", 'DimEntityD'[ExternalKey] & "",
"Location", 'DimLocationB'[LocationCode] & "",
"Direction", 'DimLocationB'[DirectionCode] & "",
"GroupLabel", 'DimGroupB'[GroupLabel] & "",
"EntityName", 'DimEntityC'[EntityName] & "",
"City", 'DimAttribute4'[City] & "",
"PostalCode", 'DimAttribute4'[PostalCode] & "",
"StatusLabel", 'DimStatus'[StatusLabel],
"Hierarchy", 'DimHierarchyB'[HierarchyLevel] & "",
"EntityType", 'DimEntityC'[EntityType] & "",
"EntitySubtype", 'DimEntityC'[EntitySubtype] & "",
"MetricB_Main", [MetricB_Main],
"MetricB_Alt", [MetricB_Alt],
"MetricB_Alt1", [MetricB_Alt1],
"MetricB_Alt2", [MetricB_Alt2],
"Flag1", [MetricB_Flag1],
"Flag2", [MetricB_Flag2],
"Flag3", [MetricB_Flag3],
"Flag4", [MetricB_Flag4],
"Flag_AM", [MetricB_FlagAM],
"Flag_PM", [MetricB_FlagPM],
"StatusFlag", [MetricB_Status]
)
//------------------------------------
// FULL OUTER JOIN
//------------------------------------
VAR JoinA =
SELECTCOLUMNS (
NATURALLEFTOUTERJOIN ( A_View, B_View ),
"Date", [Date],
"Period", [Period],
"CategoryA", [CategoryA],
"CategoryB", [CategoryB],
"CategoryC", [CategoryC],
"EntityKey", [EntityKey],
"ExternalKey", [ExternalKey],
"Location", [Location],
"Direction", [Direction],
"GroupLabel", [GroupLabel],
"EntityName", [EntityName],
"City", [City],
"PostalCode", [PostalCode],
"StatusLabel", [StatusLabel],
"Hierarchy", [Hierarchy],
"EntityType", [EntityType],
"EntitySubtype", [EntitySubtype],
"MetricA_Main", [MetricA_Main],
"MetricA_Value1", [MetricA_Value1],
"MetricA_Value2", [MetricA_Value2],
"MetricA_Value3", [MetricA_Value3],
"MetricA_Value4", [MetricA_Value4],
"MetricB_Main", [MetricB_Main],
"MetricB_Alt", [MetricB_Alt],
"MetricB_Alt1", [MetricB_Alt1],
"MetricB_Alt2", [MetricB_Alt2],
"Flag1", [Flag1],
"Flag2", [Flag2],
"Flag3", [Flag3],
"Flag4", [Flag4],
"Flag_AM", [Flag_AM],
"Flag_PM", [Flag_PM],
"StatusFlag", [StatusFlag]
)
VAR JoinB =
SELECTCOLUMNS (
NATURALLEFTOUTERJOIN ( B_View, A_View ),
"Date", [Date],
"Period", [Period],
"CategoryA", [CategoryA],
"CategoryB", [CategoryB],
"CategoryC", [CategoryC],
"EntityKey", [EntityKey],
"ExternalKey", [ExternalKey],
"Location", [Location],
"Direction", [Direction],
"GroupLabel", [GroupLabel],
"EntityName", [EntityName],
"City", [City],
"PostalCode", [PostalCode],
"StatusLabel", [StatusLabel],
"Hierarchy", [Hierarchy],
"EntityType", [EntityType],
"EntitySubtype", [EntitySubtype],
"MetricA_Main", [MetricA_Main],
"MetricA_Value1", [MetricA_Value1],
"MetricA_Value2", [MetricA_Value2],
"MetricA_Value3", [MetricA_Value3],
"MetricA_Value4", [MetricA_Value4],
"MetricB_Main", [MetricB_Main],
"MetricB_Alt", [MetricB_Alt],
"MetricB_Alt1", [MetricB_Alt1],
"MetricB_Alt2", [MetricB_Alt2],
"Flag1", [Flag1],
"Flag2", [Flag2],
"Flag3", [Flag3],
"Flag4", [Flag4],
"Flag_AM", [Flag_AM],
"Flag_PM", [Flag_PM],
"StatusFlag", [StatusFlag]
)
RETURN
DISTINCT ( UNION ( JoinA, JoinB ) )
Any help, best practices, or alternative approaches for optimizing this type of DAX query over a large fact table would be greatly appreciated.