cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TMJForrester
Frequent Visitor

Analyze in Excel with PBI Report Connected to Azure Analysis Services

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!

1 ACCEPTED SOLUTION
GilbertQ
Super User II
Super User II

Hi @TMJForrester 

 

The only way to get it working is to get those measures integrated into your Azure Analysis Services model. It will then be available to all users who connect via Excel to the AAS cube.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @TMJForrester ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.
 
Best Regards
Lucien
GilbertQ
Super User II
Super User II

Hi @TMJForrester 

 

The only way to get it working is to get those measures integrated into your Azure Analysis Services model. It will then be available to all users who connect via Excel to the AAS cube.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors