Forum Discussion
Query time is too big when adding one column to table. How to approach the problem?
I have a DAX query that performs well, but when I add a single additional column
Dim_EA_AmortizedCosts_Resources[ResourceType] — the query time increases dramatically and often exceeds the timeout limit.
The model is a standard star schema (fact table + dimensions), and the query is generated by Power BI.
Here is the full DAX query that becomes slow when ResourceType is included:
DEFINE
MEASURE 'MeasureTable'[New_AmortizedCosts] =
(/* USER DAX BEGIN */
SUM(Fct_EA_AmortizedCosts[CostInBillingCurrency])
/* USER DAX END */)
VAR __DS0FilterTable =
TREATAS({"Apr"}, 'Dim_Date'[MonthShort])
VAR __DS0FilterTable2 =
TREATAS({"'Dim_EA_AmortizedCosts_Tags'[Key]"}, 'Key Value Switcher'[Parameter Fields])
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('Dim_Date'[Date])),
'Dim_Date'[Date] < DATE(2025, 4, 2)
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Dim_EA_Resource_TagKeys'[Key],
'Dim_EA_Resource_TagKeys'[Value],
'Dim_EA_AmortizedCosts_Resources'[ResourceType]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
"New_AmortizedCosts", 'MeasureTable'[New_AmortizedCosts]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
[New_AmortizedCosts],
0,
'Dim_EA_Resource_TagKeys'[Key],
1,
'Dim_EA_Resource_TagKeys'[Value],
1,
'Dim_EA_AmortizedCosts_Resources'[ResourceType],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
[New_AmortizedCosts] DESC,
'Dim_EA_Resource_TagKeys'[Key],
'Dim_EA_Resource_TagKeys'[Value],
'Dim_EA_AmortizedCosts_Resources'[ResourceType]
What steps should I take to diagnose why adding this one dimension column (ResourceType) drastically slows down the query?
Should I look at cardinality, relationships, Auto-Exists, or something else in Server Timings / Query Plan?
My model is based on SQLBI best practices:
Hi jaryszek
When you say adding a single column such as Dim_EA_AmortizedCosts_Resources[ResourceType] causes a dramatic slowdown, it usually points to cardinality and Auto-Exists behaviour in DAX combined with the query shape. Here is how to approach the diagnosis:
- Cardinality explosion: Adding a column from a dimension with high cardinality multiplies the number of groups in SUMMARIZECOLUMNS(). If ResourceType has thousands of distinct values, the engine must compute many more combinations.
- Auto-Exists logic: When multiple dimensions are grouped, the engine checks valid combinations across relationships. This can be expensive if the relationships involve large tables.
- ROLLUPGROUP and subtotals: Using ROLLUPADDISSUBTOTAL adds extra complexity because the engine calculates subtotals for every grouping level.
Use Server Timings and Query Plan- Enable Performance Analyzer Look for:
- Storage Engine scans: Are they increasing significantly?
- Formula Engine operations: Is there a large increase in rows processed?
Test Without ROLLUP- Remove ROLLUPGROUP and ROLLUPADDISSUBTOTAL temporarily.
- Compare timings to confirm if subtotals are the main driver.
The last comment, cannot you simplify this query?
SUMMARIZECOLUMNS( 'Dim_EA_Resource_TagKeys'[Key], 'Dim_EA_Resource_TagKeys'[Value], 'Dim_EA_AmortizedCosts_Resources'[ResourceType], "New_AmortizedCosts", [New_AmortizedCosts] )If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
4 Replies
Hi jaryszek
When you say adding a single column such as Dim_EA_AmortizedCosts_Resources[ResourceType] causes a dramatic slowdown, it usually points to cardinality and Auto-Exists behaviour in DAX combined with the query shape. Here is how to approach the diagnosis:
- Cardinality explosion: Adding a column from a dimension with high cardinality multiplies the number of groups in SUMMARIZECOLUMNS(). If ResourceType has thousands of distinct values, the engine must compute many more combinations.
- Auto-Exists logic: When multiple dimensions are grouped, the engine checks valid combinations across relationships. This can be expensive if the relationships involve large tables.
- ROLLUPGROUP and subtotals: Using ROLLUPADDISSUBTOTAL adds extra complexity because the engine calculates subtotals for every grouping level.
Use Server Timings and Query Plan- Enable Performance Analyzer Look for:
- Storage Engine scans: Are they increasing significantly?
- Formula Engine operations: Is there a large increase in rows processed?
Test Without ROLLUP- Remove ROLLUPGROUP and ROLLUPADDISSUBTOTAL temporarily.
- Compare timings to confirm if subtotals are the main driver.
The last comment, cannot you simplify this query?
SUMMARIZECOLUMNS( 'Dim_EA_Resource_TagKeys'[Key], 'Dim_EA_Resource_TagKeys'[Value], 'Dim_EA_AmortizedCosts_Resources'[ResourceType], "New_AmortizedCosts", [New_AmortizedCosts] )If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Ok so let's break this query down.
The biggest cardinality i have in TagKeysValue which is:ResourceType is only 59...Key is only 327.
So Value is huge cardinality. Or not? How to refer to something in order to say if it is big or not?// DAX Query DEFINE MEASURE 'MeasureTable'[New_AmortizedCosts] = (/* USER DAX BEGIN */ SUM(Fct_EA_AmortizedCosts[CostInBillingCurrency]) /* USER DAX END */) VAR __DS0FilterTable = TREATAS({"Apr"}, 'Dim_Date'[MonthShort]) VAR __DS0FilterTable2 = TREATAS({"Application"}, 'Dim_EA_Resource_TagKeys'[Key]) VAR __DS0FilterTable3 = TREATAS({"'Dim_EA_AmortizedCosts_Tags'[Key]"}, 'Key Value Switcher'[Parameter Fields]) VAR __DS0FilterTable4 = FILTER( KEEPFILTERS(VALUES('Dim_Date'[Date])), 'Dim_Date'[Date] < DATE(2025, 4, 2) ) VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( ROLLUPGROUP('Dim_EA_Resource_TagKeys'[Key], 'Dim_EA_Resource_TagKeys'[Value]), "IsGrandTotalRowTotal" ), __DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, __DS0FilterTable4, "New_AmortizedCosts", 'MeasureTable'[New_AmortizedCosts] ) VAR __DS0PrimaryWindowed = TOPN( 502, __DS0Core, [IsGrandTotalRowTotal], 0, [New_AmortizedCosts], 0, 'Dim_EA_Resource_TagKeys'[Key], 1, 'Dim_EA_Resource_TagKeys'[Value], 1 ) EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, [New_AmortizedCosts] DESC, 'Dim_EA_Resource_TagKeys'[Key], 'Dim_EA_Resource_TagKeys'[Value]
this is a query run for only Key and Value.
If SE and FE proportions are fine?
How to increase the speed now?Hi jaryszek,
Thank you Zanqueta, for your insights.
Query performance drops when ResourceType is included because Power BI must expand a complex many-to-many relationship and group data across a high-cardinality, snowflaked Tag dimension, increasing the workload on both the front-end and storage engine. To improve this, consider simplifying the model by combining TagKeys, TagValues, and ResourceType into one dimension, using numeric surrogate keys instead of high-cardinality text fields, and removing unused Tag columns. These steps reduce expanded table size and eliminate expensive many-to-many joins, leading to better summarization performance.
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Data reduction techniques for Import modeling - Power BI | Microsoft Learn
Thank you.
