Background
I am a report developer with a Power BI Pro license (no Premium unfortunately). My company has developed an enterprise tabular model using Azure Analysis Services which stores a few billion rows of sensitive financial data & is managed by a separate enterprise BI team. The vast majority of our Power BI reports are built with a live connection to this Azure AS model in order to standardize our financial reporting. The model itself contains basic aggregate measures (i.e. "Sum Revenue", "Sum Direct Costs", etc.), but my team often builds more complex DAX measures stored in the local Power BI file depending on each report's specific requirements. Some examples of measures we build are simple time intelligence calculations, but others are pretty complex aggregations filtering multiple tables in the data model.
Situation
Our front-end customers want to export data from our local Power BI reports into Excel so they can conduct their own data analysis on their own time. Our team is trying to help our customers get more comfortable with keeping the reports in the cloud, but they've been using Excel for years and the learning process is slow.
So far they've been exporting data from Power BI into Excel, but due to the quantity of data, end users quickly reach Power BI's export limits and don't know how to reformat the exported data to match the Power BI visual. Another issue is that some of our measures used to calculate margins don't aggregate correctly when put into an Excel Pivot table. Our customers aren't the most tech-savvy people so they might not know how to build calculated fields to rebuild these correctly.
Using "Analyze in Excel" seems like the best option here, but because our reports are connected live to an Analysis Services model, the Analyze in Excel feature will only show the basic measures stored in the AS model: not the more complex ones in my local Power BI report. I've even tried selecting Analyze in Excel from Power BI Desktop and unfortunately that also connects to the data model instead of my local report.
It seems like the only option available is to export data from the Azure model, store it in a separate database, and then import that data into a local Power BI report so people can use Analyze in Excel with my local measures. I would prefer not doing this as it defeats the purpose of my company's investment in Azure, but I really don't know what else to do here.
Question
Has anyone been able to think of a workaround for this situation? Any help would be greatly appreciated!
