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.
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"
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
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:
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?)
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 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?
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:
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.
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?
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.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates