SSAS Tabular Cube 2 billion rows error when using Calculation Group

Hi Anto11 ,

The error "table contains more than two billion rows" in this specific context (Excel + Calculation Groups) usually indicates that the MDX query generated by Excel is trying to materialize an intermediate result set that exceeds the 32-bit row limit (approx. 2.1 billion), rather than a storage limit on your partitions.

This commonly happens due to how Excel interacts with "Implicit Measures" when Calculation Groups are active.

Here is the checklist to resolve this:

1. Enable "Discourage Implicit Measures" (Most Likely Fix) In your Tabular Model, select the Model object properties. Set Discourage Implicit Measures to True.

• Reason: When this is False (default), Excel treats every numeric column as a potential measure. When you add a Calculation Group to rows/columns, Excel generates a query trying to cross-join your 2B+ row table with the calculation items. Setting this to True forces Excel to use explicit DAX measures, which drastically optimizes the query plan.

2. Check Dynamic Format Strings If your Calculation Items use dynamic FormatString expressions, the engine might be forced to evaluate the query at the row level to determine the correct format for every single row, hitting the limit. Try removing the Format String expression temporarily to verify if this is the cause.

3. Partitioning Verification Ensure your 'Sales' table is actually partitioned (e.g., by Year or Month). While Tabular 2019 supports tables larger than 2 billion rows, you cannot have a single partition exceeding 2 billion rows unless you are using specific large-table settings, but splitting it is the standard best practice.

Summary: Start by setting Discourage Implicit Measures = True. This is practically a requirement when using Calculation Groups with Excel clients.

If this solves the error, kindly mark it as the Accepted Solution!
This response was assisted by AI for translation and formatting purposes.