Hello,
I am asking this question again because my initial post did not receive an answer, and I urgently need help to resolve this issue.
I have a DAX query running inside a paginated report. The purpose of the report is to generate an extraction for the client.
When I deploy the report to the Power BI Service and execute it, I receive the following error:
Unable to render paginated report
There was an error communicating with Analysis Services. Resource Governance: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either simplify the query or its calculations, or if using Power BI Premium, you may reach out to your capacity administrator to see if they can increase the per-query memory limit. More details: consumed memory MB, memory limit MB. See https://go.microsoft.com/fwlink/?linkid=2159752 to learn more.
What I have tried so far (issue still persists):
As bellow my last version of the query :
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.
