How can I retrieve the list of measures contained in a SSAS connected Power BI report?

Question

I am working on a PBI report created by someone else in the organisation and I need to do some auditing of all the measures (50 or more) contained in the report itself.

The report connects to an on-premises instance of SQL Server Analysis Services.

I am trying to get the list of all measures contained in the report. To achieve that, in previous occasions, I used DAX Studio to connect to the running instance of the PBI Desktop as described in https://exceleratorbi.com.au/getting-started-dax-studio/ .

However, as this report connects to SSAS, when I try to connect DAX Studio to it, I get an error: "No Databases were found when connecting to PBI Desktop. If your PBI file is using a Live Connection please connect directly to the source model instead."

Is there another known method I can use to extract all measures from the PBIX itself?

Just connect to the SSAS directly, as the error message suggests. — Andrey Nikolov
As described in the question, my measures are not in the SSAS model but in the PBIX file instead. — rafoztr

GregGalloway GregGalloway · Accepted Answer · 2019-12-05T16:26:03

If you rename the .pbix file to .zip and open it as a zip file you will see a Report folder and then a file called Layout. If you copy that file out of the .zip file and open it in a text editor (preferably an app which can format JSON) you will see the following:

{
  "id": 0,
  "resourcePackages": [
    //some packages here
  ],
  "sections": [
    //some sections here...
  ],
  "config": "{\"version\":\"5.3\",\"themeCollection\":{\"baseTheme\":{\"name\":\"CY19SU06\",\"version\":\"5.5\",\"type\":2}},\"activeSectionIndex\":0,\"modelExtensions\":[{\"name\":\"extension\",\"entities\":[{\"name\":\"DimDate\",\"extends\":\"DimDate\",\"measures\":[{\"name\":\"My Report Measure\",\"dataType\":3,\"expression\":\"DIVIDE(99,100)\",\"errorMessage\":null,\"hidden\":false,\"formulaOverride\":null,\"formatInformation\":{\"formatString\":\"G\",\"format\":\"General\",\"thousandSeparator\":false,\"currencyFormat\":null,\"dateTimeCustomFormat\":null}}]},{\"name\":\"DimCustomer\",\"extends\":\"DimCustomer\",\"measures\":[{\"name\":\"My Report Measure 2\",\"dataType\":3,\"expression\":\"99 + 100\",\"errorMessage\":null,\"hidden\":false,\"formulaOverride\":null,\"formatInformation\":{\"formatString\":\"G\",\"format\":\"General\",\"thousandSeparator\":false,\"currencyFormat\":null,\"dateTimeCustomFormat\":null}}]}]}],\"defaultDrillFilterOtherVisuals\":true,\"settings\":{\"useStylableVisualContainerHeader\":true,\"exportDataMode\":1,\"useNewFilterPaneExperience\":true,\"allowChangeFilterTypes\":true},\"objects\":{\"section\":[{\"properties\":{\"verticalAlignment\":{\"expr\":{\"Literal\":{\"Value\":\"'Top'\"}}}}}]}}",
  "layoutOptimization": 0
}

If you look in the config property there's a string which has JSON in it. If you pull out the JSON and format it you will get:

{
  "version": "5.3",
  "themeCollection": {
    "baseTheme": {
      "name": "CY19SU06",
      "version": "5.5",
      "type": 2
    }
  },
  "activeSectionIndex": 0,
  "modelExtensions": [
    {
      "name": "extension",
      "entities": [
        {
          "name": "DimDate",
          "extends": "DimDate",
          "measures": [
            {
              "name": "My Report Measure",
              "dataType": 3,
              "expression": "DIVIDE(99,100)",
              "errorMessage": null,
              "hidden": false,
              "formulaOverride": null,
              "formatInformation": {
                "formatString": "G",
                "format": "General",
                "thousandSeparator": false,
                "currencyFormat": null,
                "dateTimeCustomFormat": null
              }
            }
          ]
        },
        {
          "name": "DimCustomer",
          "extends": "DimCustomer",
          "measures": [
            {
              "name": "My Report Measure 2",
              "dataType": 3,
              "expression": "99 + 100",
              "errorMessage": null,
              "hidden": false,
              "formulaOverride": null,
              "formatInformation": {
                "formatString": "G",
                "format": "General",
                "thousandSeparator": false,
                "currencyFormat": null,
                "dateTimeCustomFormat": null
              }
            }
          ]
        }
      ]
    }
  ],
  "defaultDrillFilterOtherVisuals": true,
  "settings": {
    "useStylableVisualContainerHeader": true,
    "exportDataMode": 1,
    "useNewFilterPaneExperience": true,
    "allowChangeFilterTypes": true
  },
  "objects": { "section": [ { "properties": { "verticalAlignment": { "expr": { "Literal": { "Value": "'Top'" } } } } } ] }
}

You will see My Report Measure in the DimDate table which is expression DIVIDE(99,100) and My Report Measure 2 in the DimCustomer table which is expression 99 + 100. Those are simplistic examples but that gives you the idea.

Obviously this is all undocumented and subject to change. But that's the only way I'm aware of to get these measures which are added to the PBIX (rather than measure in the SSAS model itself).

How can I retrieve the list of measures contained in a SSAS connected Power BI report?

1 Answers