Extracting Report Level measures DAX using SQL Profiler output

[XLGeek]

In the May 2017 release of Power BI Desktop a new ‘report measures’ feature was released. It allows users who have connected Power BI Desktop to an external Analysis Services tabular model to write their own measures using DAX. These measures are stored within the report, and sent in the queries back to the underlying model. Any user who has access to query the model can do this – just the same as if they were using Excel or SQL Management Studio to connect to the SSAS server.

Analysis Services admins will want to know what report measures are being used against their models. If there are common measures, or measures used by many users, these should be considered as candidates to move into the central model itself. We’ve created a Power BI Desktop template that IT professionals with Analysis Services instance administrator access can use against SQL Profiler output to accomplish this. Download the attached file (created by Power BI's Will Thompson) and follow these steps:

1. Start SQL Profiler and point to the Analysis Service model you want to track for report measure usage. At the minimum, you need to select “Query Begin” events (using SQL Profiler’s default event template will also work), but don’t change the default column selection.
2. Use Power BI Desktop to generate some queries using report measures (or let your users do this!). For example, open the Power BI Desktop report that contains report level measures and apply some filters or build some visuals to make sure queries are issued against the Analysis Services data source, so that the query begin events can be captured in the SQL Profiler.
3. Stop SQL Profiler and save the output as “Trace XML File…”.
4. Open “Report measures.pbit” file and when the “File location” prompt comes up, input the path to the profiler trace file in XML format that you saved in step 3:
   
5. Click “Load” button. You should see all your report level measure names and their DAX expressions displayed in the table visual of the provided report.
6. If you want to export the DAX formulas to use in your own measures, click the … in the top right of the table and choose Export Data

Report measures monitoring PBIT.zip

[arobin]

Hi Team,

Good Day.

I was able to update my PowerBI desktop to May 2017 version yesterday. However after the update, when I refresh my query it throws out some problems I never experienced before. This message came out upon clicking refresh button. This never came out before when I try to refresh my query.then this upon clicking "Run"

I tried to log in using the same credentials in MS SQL Server Management Studio and was able to connect.

Can you please help what causes the two messages to appear. They were not there in older version.

Thanks...