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