Forum Discussion
Memory issue with Excel Pivot table connected to Semantic Models
Hi!
Since a couple of weeks ago we have a lot of Excel Pivot tables connected to Semantic Models that fails because we exceed the memory limit of 10 GB. We have not done any change to our Semantic Models and not to our Excel Pivot tables and they all worked fine before.
Do any one know what the problem can be? Has there been any change how Power BI handle the MDX question generated by the Pivot table or has there been any change in Excel how the MDX question is generated in the Pivot table?
Regards
Fredrik
Hi!
I have now downgraded the Excel version on my computer and can confirm that the problem is the Excel version. Version 16.0.18429.20132 was installed on my computer and now I have downgraded it to version 16.0.18227.20082.
Our files that exceeded the 10 GB memory limit now only consume 50 MB.
The MDX code generated in version 16.0.18429.20132 of Excel looked like this:
SELECT
NON EMPTY
Hierarchize
(
DrillDownMember
(
DrillDownMember
(
CrossJoin
(
{
[Product].[Article].[All],
[Product].[Article].[Article].ALLMEMBERS
},
{([Supplier].[Production Market].[All],[Size].[Size].[All])}
),
[Product].[Article].[Article].ALLMEMBERS,
[Supplier].[Production Market]
),
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size]
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{
[Time DIS].[Period DIS].&[Aug, 2025],
[Time DIS].[Period DIS].&[Dec, 2025],
[Time DIS].[Period DIS].&[Jul, 2025],
[Time DIS].[Period DIS].&[Jun, 2025],
[Time DIS].[Period DIS].&[May, 2025],
[Time DIS].[Period DIS].&[Nov, 2025],
[Time DIS].[Period DIS].&[Oct, 2025],
[Time DIS].[Period DIS].&[Sep, 2025]
} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - Casual W],
[Product].[Major Product Group].&[6 - Trousers],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
And the MDX code for the same Pivot table but in version 16.0.18227.20082 of Excel looks like this:
SELECT
NON EMPTY
Hierarchize
(
CrossJoin
(
{[Product].[Article].[Article].ALLMEMBERS},
{
(
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size].[Size].ALLMEMBERS
)
}
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{
[Time DIS].[Period DIS].&[Aug, 2025],
[Time DIS].[Period DIS].&[Dec, 2025],
[Time DIS].[Period DIS].&[Jul, 2025],
[Time DIS].[Period DIS].&[Jun, 2025],
[Time DIS].[Period DIS].&[May, 2025],
[Time DIS].[Period DIS].&[Nov, 2025],
[Time DIS].[Period DIS].&[Oct, 2025],
[Time DIS].[Period DIS].&[Sep, 2025]
} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - Casual W],
[Product].[Major Product Group].&[6 - Trousers],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
4 Replies
Hello FredrikJ
Thank you for reaching out to the Microsoft Fabric Community. We understand that your Excel Pivot Tables connected to Semantic Models are now exceeding the 10 GB memory limit, despite no changes to the models or Pivot Tables.This issue may be due to changes in how MDX queries are processed in Power BI or Excel.
- Microsoft frequently updates Power BI and Excel, which could have altered MDX query execution behavior.
- Check if any recent updates to Power BI or Excel were installed around the time the issue began. If so, consider rolling back to an earlier version to see if the problem persists.
- If your Premium Capacity SKU has a 10 GB memory limit, consider upgrading to a higher capacity tier, such as F32, which offers 12 GB per-query memory.
- If you are using Power BI Premium Per User (PPU), transitioning to Premium Capacity could provide higher memory limits.
Utilize the Power BI Capacity Metrics App to monitor memory consumption and determine if your Semantic Model is experiencing increased resource usage.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you!
Hi!
Thanks for your answer. We got a F128 and already use max memory per query which is 10 GB
I will check the Excel updates and see if we can do a rollback. But we are using Power BI Service so in that case I guess it is impossible to do a rollback since we can't control the updates in the service?
Regards
Fredrik
Hi!
I have now downgraded the Excel version on my computer and can confirm that the problem is the Excel version. Version 16.0.18429.20132 was installed on my computer and now I have downgraded it to version 16.0.18227.20082.
Our files that exceeded the 10 GB memory limit now only consume 50 MB.
The MDX code generated in version 16.0.18429.20132 of Excel looked like this:
SELECT
NON EMPTY
Hierarchize
(
DrillDownMember
(
DrillDownMember
(
CrossJoin
(
{
[Product].[Article].[All],
[Product].[Article].[Article].ALLMEMBERS
},
{([Supplier].[Production Market].[All],[Size].[Size].[All])}
),
[Product].[Article].[Article].ALLMEMBERS,
[Supplier].[Production Market]
),
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size]
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{
[Time DIS].[Period DIS].&[Aug, 2025],
[Time DIS].[Period DIS].&[Dec, 2025],
[Time DIS].[Period DIS].&[Jul, 2025],
[Time DIS].[Period DIS].&[Jun, 2025],
[Time DIS].[Period DIS].&[May, 2025],
[Time DIS].[Period DIS].&[Nov, 2025],
[Time DIS].[Period DIS].&[Oct, 2025],
[Time DIS].[Period DIS].&[Sep, 2025]
} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - Casual W],
[Product].[Major Product Group].&[6 - Trousers],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
And the MDX code for the same Pivot table but in version 16.0.18227.20082 of Excel looks like this:
SELECT
NON EMPTY
Hierarchize
(
CrossJoin
(
{[Product].[Article].[Article].ALLMEMBERS},
{
(
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size].[Size].ALLMEMBERS
)
}
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{
[Time DIS].[Period DIS].&[Aug, 2025],
[Time DIS].[Period DIS].&[Dec, 2025],
[Time DIS].[Period DIS].&[Jul, 2025],
[Time DIS].[Period DIS].&[Jun, 2025],
[Time DIS].[Period DIS].&[May, 2025],
[Time DIS].[Period DIS].&[Nov, 2025],
[Time DIS].[Period DIS].&[Oct, 2025],
[Time DIS].[Period DIS].&[Sep, 2025]
} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - Casual W],
[Product].[Major Product Group].&[6 - Trousers],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;
Hi!
I've done some more testing and here's some more information.
I've set up a simple pivot that goes through to see how much memory it requires. The pivot I set up generates this MDX code:
SELECT
NON EMPTY
Hierarchize
(
DrillDownMember
(
DrillDownMember
(
CrossJoin
(
{
[Product].[Article].[All],
[Product].[Article].[Article].ALLMEMBERS
},
{([Supplier].[Production Market].[All],[Size].[Size].[All])}
),
[Product].[Article].[Article].ALLMEMBERS,
[Supplier].[Production Market]
),
[Supplier].[Production Market].[Production Market].ALLMEMBERS,
[Size].[Size]
)
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
(
SELECT
{[Time DIS].[Period DIS].&[Sep, 2025]} ON COLUMNS
FROM [Model]
)
WHERE
(
[Product].[Collection].&[46 - W],
[Product].[Major Product Group].&[6 - T],
[Measures].[Ord Units]
)
CELL PROPERTIES
VALUE,
FORMAT_STRING,
LANGUAGE,
BACK_COLOR,
FORE_COLOR,
FONT_FLAGS;And this query uses 3 GB of memory.
The model is a total of 600 MB
The dimensions used are:
Product containing 230,000 rows
Supplier containing 2,400 rows
The facts table, FPURORD, used contains 1.1 million rows
The measure used looks like this: SUM('FPURORD'[POUNITS])
I seem like a lot of memory usage for this small model and simple pivot table
