Currently Power BI has an export limit: 30,000 rows when you export to CSV and 150,000 when you export to Excel.
There are a lot of complaints about this limit. In many cases the limit is a deal breaker when companies decide which reporting tool to use. Ideally the limit would be configured by Power BI administrators.
The good news is that there’s a way to overcome this limit, but it requires a little development effort. First, you must have Power BI Premium, because you’ll need to use Power BI embedded. At this time Microsoft only allows a connection to the Power BI dataset published on a Premium node (however they have promised to change this in the future).
Here are the steps:
1. Embed your report in a web app using Power BI embedded. Here’s the ‘Power BI Embedded Playground’ with examples of the API calls.
3. Using SQLBI’s DAX Studio, capture the DAX query which Power BI sends to the dataset to get the export, you can capture it either connecting to Power BI desktop or the Power BI dataset published on the server.
4. The DAX query generated in the previous step (please see the example at the end of this post) contains three main parts:
DEFINE This part of the query defines your filters. You will need to change it in order to dynamically define the filters based on your slicer states collected during step #2. The good news is that each variable defined in this part will match one of your slicers, so creating the DAX expression based on the slicers states is not very difficult.
EVALUATE TOPN(30002) This is the part where Microsoft hardcoded the number of exported rows. TOP 30002 means that the query will return only first 30002 of the result (1 row of the header + 30001 rows of result). Now you can change it to any number you want (I changed it to 300,000).
ORDER BY You can remove this from the query. If you export it to Excel the users can re-order the data the way they want anyhow.
From your web application you will connect to the Power BI dataset. Here are a few resources which will help:
- Connecting to Azure Analysis Services using Service principal or User token
- Using the Power BI Rest API’s as a data source in Power BI
Here’s the NuGet package for .Net 4.6 I used : Microsoft.AnalysisServices.AdomdClient.retail.amd64 version 16.3.0.
You can use the similar component from .Net Core if you need to run it on Linux or Mac.
6. Execute the DAX with dynamically generated filters created in step #4 against the Power BI dataset using the connection created in step #5. I used command.ExecuteReader().
Here’s an example of the DAX query generated by Power BI when exporting to CSV:
VAR __DS0FilterTable =
VAR __DS0FilterTable2 =
VAR __DS0FilterTable3 =
VAR __DS0FilterTable4 =
VAR __DS0FilterTable5 =
‘FactResellerSales'[ShipDate] ), “IsGrandTotalRowTotal”
“Total_Sales”, ‘FactResellerSales'[Total Sales],
Vlad Gedgafov is a Senior BI Consultant at DesignMind.