Forum Discussion
Solution SageDetailRow View in AAS does not work if table has more than 2 Billion Records
This question relates to our Azure Analysis Services semantic model rather than Power BI directly.
We have enabled the Detail Rows (drillthrough) functionality for one of our measures. However, the underlying fact table has now exceeded 2 billion rows. Since crossing this threshold, the Detail Rows functionality no longer works as expected.
We suspect this may be related to engine limitations or rowset size constraints when materializing the detail result set.
Is there a supported approach to enable Detail Rows functionality in this scenario without reducing the size of the fact table (e.g., configuration changes, query limits, or alternative modeling strategies)?
Hi sayaliredij
In Azure Analysis Services/Tabular there are two separate constraints: (1) drillthrough rowset return limits (e.g., DefaultDrillthroughMaxRows, RowsetSerializationLimit) and (2) engine evaluation paths that can fail when a referenced table has >2 billion rows. The second category cannot be solved by increasing MAXROWS/Rowset limits; it requires avoiding evaluation over the 2B+ table.
A supported approach is to redesign the Detail Rows Expression so it returns rows from a smaller “detail” table (e.g., transaction header/recent window/aggregated detail) and/or offload raw row retrieval to DirectQuery or an external parameterized report/endpoint. If the requirement is truly to return line-level rows from the 2B+ fact, consider splitting the fact into multiple physical tables so no single table exceeds the 2B threshold.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
3 Replies
- Ritaf1983
Super User
Hi sayaliredij
In Azure Analysis Services/Tabular there are two separate constraints: (1) drillthrough rowset return limits (e.g., DefaultDrillthroughMaxRows, RowsetSerializationLimit) and (2) engine evaluation paths that can fail when a referenced table has >2 billion rows. The second category cannot be solved by increasing MAXROWS/Rowset limits; it requires avoiding evaluation over the 2B+ table.
A supported approach is to redesign the Detail Rows Expression so it returns rows from a smaller “detail” table (e.g., transaction header/recent window/aggregated detail) and/or offload raw row retrieval to DirectQuery or an external parameterized report/endpoint. If the requirement is truly to return line-level rows from the 2B+ fact, consider splitting the fact into multiple physical tables so no single table exceeds the 2B threshold.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
v-karpurapudCommunity Support
Hi sayaliredij
Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to Ritaf1983 for offering helpful suggestions.
Could you let us know if the suggested solution resolved your issue? If it's still pending, please let us know any further details so we can help.
Best regards,
Community Support Team.- v-karpurapud
Hi sayaliredij
We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank You.