cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EylesIT
Post Patron
Post Patron

How to export many DAX measures from Power BI report into SSAS tabular model?

Not sure if "Desktop" this is the correct forum for my question.

 

I have developed a Power BI Desktop report which uses an SSAS tabular model as it's source. I have have created about 150 measures in the report. I now want to add all of those measure definitions into the SSAS tabular model (so that other report developers can user them in their reports). 

How do I export all the measures from the Power BI report into the tabular model? I hope there is a way without going to every one of the measures, copying and pasting the measure DAX into the tabular model project?

 

I have tried using DAX Studio v2.15 to connect to my open Power BI report (via Data Source option PBI/SSDT Model), but it fails with the error "No Databases were found when connecting to localhost:xxxxx (PowerBIDesktop). If your Power BI File is using a Live Connection please connect directly to the source model instead." Connecting to the source model as suggested defeats the purpose because the measures I want to export reside in the Power BI report.

15 REPLIES 15
EylesIT
Post Patron
Post Patron

I have submitted this as a Power BI Idea "Mechanism to allow Report Level Measures to be pushed from Power BI report into an SSAS tabular model"

https://ideas.powerbi.com/ideas/idea/?ideaid=e4b6fd40-eccc-eb11-ba5e-281878bdb01d

 

PaulDBrown
Super User II
Super User II

It may well be because it's direct query. It might be worth a try to create a copy of the file, change the connection to import mode to be able export via PowerBI Helper





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Is it possible to change the mode from Direct Query to Import? I can't see how to do that. Even if I could, this would not work - the Analysis Servces database is 100GB so it will never be able to be Import mode.

You can change from Direct Query to Import mode:

https://community.powerbi.com/t5/Desktop/How-to-change-direct-query-to-import-query/m-p/100996 

but I would assume you will need a smaller sample of data. (I'm out of my depth here, but if you can produce a smaller sample, you could use that for the purpose of exporting the measures only, no?)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks for the link. Unfortunately this does not work when teh Data Source is an Analysis Services database.

Sorry, I'm really out of my depth here, so can't be of more help...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






That's ok, I really appreiate all your responses and help. While I've not solved the problem yet, I've learned a lot from you along the way. Thanks!

PaulDBrown
Super User II
Super User II

It might be worth checking out PowerBI Helper from the folks at Radacad:

https://radacad.com/power-bi-helper 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown I don't think PowerBI Helper can do this. In the feature comments page, Lorenzo asks in Jan 2020 "can we use power bi helper to detect Report level measures code into a Report working in live connection" and tehr reply was "Not yet, we will add it soon in the next versions". Nothing in the features list after Jan 2020 suggests the tool can now "detect Report level measures"

Have you been able to achieve this with the tool?

@EylesIT 

I'm not sure if this is what you need, but I just tried the export metadata option in PowerBI Helper and one of the exported csv files returns the measure expressions:

measure list.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






This is frustrating because Power BI Helper doed indeed identify the Report Level Measures and their expressions and displays them in the Visualisations tab. It simply does not export them.

EylesIT_0-1623341537955.png

 

That looked promising. I installed PowerBI Helper, selected my Power BI report file from the Visualisation tab (by clicking Select Power BI File and navigating to my .PBIX file). The Report Level Measures tab does indeed display the measures that have been defined in the report along with the DAX. 

However, when I click Export to Document or Export All Metadata, none of the files produced contain the measure DAX definitions. Is there a trick do doing this?

This is how I did it:

PBI Metadata.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






As a test, I created a Power BI report in the same version I'm using, made it connect to an SQL Server source (not an Analysis Services source) and created a few measures in the report. The Power BI Helper does indeed display and export those measures.

 

I then created another Power BI report in the same version I'm using, made it connect to an Analysis Services source and created a few measures in the report. The Power BI Helper again does not display and export those measures.

 

The problem must be that Power BI Helper cannot export the measures that are created within a Power BI report when that report uses an Analysis Services data source. I have emailed Radacad to report the bug.

 

Thanks very much for taking the time to post that GIF. When I follow the same steps (open the report in Power BI Desktop, select Power BI Helper from External Tools, click Connect to Model) I see that Power BI Helper does find the report and populates the "Choose the Power BI File" correctly. However, when you do that, you get the Tables, Columns, Measure etc boxes populated with data; when I do it, these boxes remain empty. So when I click "Export all metadata"), I unfortunately get this message "0 files have been exported"

 

I think this may be due to my Power BI report using an Analysis Services database as it's data source in Direct Query mode. 

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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors