Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
https://ideas.powerbi.com/ideas/idea/?ideaid=e4b6fd40-eccc-eb11-ba5e-281878bdb01d
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
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?)
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...
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!
It might be worth checking out PowerBI Helper from the folks at Radacad:
https://radacad.com/power-bi-helper
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?
@Anonymous
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:
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.
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |