delete unused measures in cube without breaking pbix?

[ovonel]

I have 3 .pbix  files that consume live from a SSAS cube.

The .bim cube has thousands of measures (most of them not used at all).

My goal is to delete the unused measures in the .bim (without breaking any visual in the .pbix).

Is there a way of opening the 3 .pbix and extracting all the measures/columns used? (or a way of seeing in the .bim which measures are unused?

Ps: each .pbix has 15 pages, so its hard to do it manually.

[lbendlin · Accepted solution]

To know if a SSAS measure is used you would have to enumerate all visuals across all pages in each of the PBIX files and fetch the query definition for each of the queries. Then you would have to check if any of the measures is mentioned.

The closest you can get to that is Get pages and visuals in a Power BI embedded analytics application | Microsoft Docs

As you probably know, a PBIX file is an archive. If you look inside it the Report\Layout file has JSON-y data about the visuals. It does not have the queries though, only the queryName.

If you pretty up that JSON you can sort of glimpse the fields that are shown in the visual. 

But the query itself is not there. Not sure where to look for that. I guess the only remedy would be to run DMV queries against the XMLA endpoint (either the local SSAS server or the service dataset)

select * from $SYSTEM.TMSCHEMA_MEASURES

And then you can judge if your SSAS measures are used in any of the DAX measures.