Extracting data from dataset using XMLA or REST API (how to get DAX)

[Anonymous]

Hi

We have Power BI Premium embedded reports. We would like to extract data from them to Excel, however there is a row limit. We would like to use Paginated reports, however they do no currently support dynamic dataset binding (1 report for multiple data sets pointing to different sources. I then looked into using the XMLA endpoint but this requires the DAX part of the query. 

To get DAX, from the embedded report is proving difficult (can't find a way with the client Javascript API just) now. 

This article got me thinking of this approach. https://sqlitybi.com/how-to-export-data-from-power-bi-using-xmla-endpoints/

Any help with this (or a suggestion of different options) would be greatly appreciated. 

For background, we have users who want their data in excel from our website once they have the report set up and it can be significantly large.

thanks in advance!

[Anonymous · Accepted solution]

The following link may be of help to us. https://www.designmind.com/business-intelligence/power-bi-tips-how-to-increase-the-number-of-rows-ex... . We would need to extract the slider/filter values on the fly and combine these with the previously extracted DAX query  to extract from the dataset.

[collinq]

Hi @Anonymous ,

To me, this is first a process/business approach issue.  Excel has a limit of 1 million rows.  I don't know how large your data is but you indicated twice that Excel row limits are a problem.  But, if you are having people take your Power BI reports and then just exporting them to Excel - then why are you using Power BI?  The capability of Power BI is so much more than Excel in so many ways (not just the visuals) that you are basically minimizing the report output by putting it into Excel.

That said, the first solution I would have if the end users MUST have their data in Excel would be to just make an Excel connection and let them know that they will only get the first 1 million rows.  And thereby you just bypass Power BI entirely.  I don't like that solution but if the report has to be in Excel, that would get rid of trying to use Power BI and getting it to export.

The second idea I have is that you create the report with very specific slicers that would minimize the number of rows that are being viewed.  That way, the user could open the Power BI report, set the slicers in such a way that the output will not be that large.  Then, they can export that output to Excel and be able to use it as they prefer.

Until the paginated report and dynamic binding is released, that may be the best approach.  I don't know under the covers too deeply but I do see on the Power BI Roadmap that "Power BI Embedded Gen2" is suppossed to come around this time of year and maybe there are more capabilities in Gen2....

[Anonymous]

Many thanks for your rapid response and time to respond. We are using embedded Power BI to provide reporting to a wide client base. These clients will use Power BI for setting up the filtering and getting aggregated values, however a large number also want to take the underlying data and import it into their own systems for secondary analysis. 

The paginated reports is our preferred route, but as you mention, it is not there yet. This is to see if there is another way of doing it. XLMA endpoints and that Power BI datasets share the same technology as Azure Analysis Service Tabular model  made me wonder if we could go direct to the dataset, but we look to need the DAX executed against the dataset to ensure the data is the same.

I appreciate this is a niche requirement (and we have challenged it with our Product team!), but it is something we need to solve (hopefully Paginated Reports deliver soon!). thanks again.