When I started at my current job, much of the new Power BI development occurred in one giant tabular model. At some point, the model had almost 1200 measures. Having so many measures in the model made it hard to edit and easy to get confused when using the measures. One workaround was a prefix for a specific media channel (For example, all radio-related measures had Radio_ prefix), yet some common measures from the main fact table still could be confusing. Lastly, even though for complex measure development I used DAX studio, bringing them into the main model could take hours.
Over the years, many measures were no longer used and simply taking up space in the model. Below is the process that I used to clean up the model using a few free tools.
Step 1. Use the Vertipaq analyzer to find all the measures columns and relationships in the model. Using the tool on a sample campaign file can give you a list of all measures in the model. A connection to the desktop is a bit tricky, but this blog by SQLBI does a great job walking through the connection process. Having this view of measures is crucial when determining which measures are no longer needed, and then tracing dependencies. It is a good idea to disconnect this file from the model to have a copy of all measures before the clean up, in case you will need to restore some of them.
Step 2: Use PowerBI Field Finder tool to determine which measures/columns are currently in use. This amazing tool allows you to connect to the PBIX file as a source and create a list of measures/columns currently in use in your reports. Export a file with measures for your references. Then, for measures (since they are unique), use a VLOOKUP against the excel file in step 1 to determine which ones are currently utilized in the reports. The tool does not list references used in the utilized measures. For that one can use Tabular Editor (step 3), checking references in the DAX code of the excel file, and in some cases, removed measures might need to be restored.
Step 3. Use the Tabular Editor tool to trace dependencies of the measures that you are about to remove. After checking dependencies and verifying whether those are in use or not, some measures can be removed from the model.
Step 4. To make sure that all reports are not missing any measures, I suggest making a copy of the report/model and testing any changes there. In case of tabular model that supports multiple reports, making a test copy is even more important. After verifying that test reports work as expected, apply the changes to the production versions of the reports/models.
