Handling Large Dataset for Granular Analysis in Power BI in a tabular visual

[Ben9987]

Dear Team,

I hope this message finds you well.

I am currently facing a challenge regarding data visualization in Power BI, and I would appreciate your assistance in resolving it.

Here's the situation: In our Azure Databricks environment, we have a table containing approximately 5 billion records spanning a period of two years, with an average of 150-200 million records per month. This table is connected to Power BI in Direct Query mode.

Recently, a requirement has arisen where users need to analyze measures at the most granular level, necessitating the display of attribute and measure distributions in a tabular visual. However, when attempting to visualize this data, an error is encountered stating: "The resultset of a query to the external data source has exceeded the maximum allowed size of '1000000' rows."

I am seeking guidance on how best to address this issue and achieve the desired outcome efficiently.

Thank you for your attention to this matter, and I look forward to your guidance.

[AnalyticsWizard]

In DirectQuery mode, the VertiPaq engine does not store a copy of the data in memory, and the data model does not need to be processed in advance. However, when DirectQuery generates a query to SQL Server, it retrieves only a predefined maximum number of rows from SQL Server, which is one million by default. This behavior is designed to limit queries that could run too long and request too much memory on Analysis Services during more complex queries. For example, certain DAX queries might transfer a large number of rows to Analysis Services for subsequent evaluation. If the result exceeds one million rows, Power BI shows the aforementioned error message. To address this issue, you can manually edit the msmdsrv.ini configuration file in your SSAS instance to specify a different limit for the MaxIntermediateRowsetSize setting. This setting is not present by default and can be added to the file to adjust the row limit.2

To address the issue of exceeding the one-million-row limit in Power BI DirectQuery mode, consider adjusting the MaxIntermediateRowsetSize setting in your SSAS instance. This will allow you to efficiently achieve the desired outcome for analyzing measures at the most gr...1

[Ben9987]

Hi @AnalyticsWizard 

Thank you for your response would definitely give it a try and let you know

[samratpbi]

There is a tool limitation for the direct query to fetch maximum no of rows. As per my understanding no way to overcome this. You may add some slicers in the report to limit no of rows before arriving on the data page (may be create a home page for slicers and from there after applying slicer, go to data display page).

[Ben9987]

Hi @samratpbi 

Thank you for your response.But the current requirement is to see the data at a granular level first and then applying the filters as per requirement .