Using "What if? analysis" to achieve higher levels of optimization in Power BI

Ever needed to clean up a report because it was getting too big and too slow? Was it hard to know what could or should be removed because the dependencies weren't clear? That's what this post is about.

One of the main purposes of our product Measure Killer is to do exactly that within Power BI, by analyzing data models and determining what is being used or not. Our new tool "What if? analysis" takes that one step further by aiding the removal of underused artifacts too.

To lower its size, the first step is to run Measure Killer and remove all the unused measures and columns from the report, freeing up space. After that, the report consists of 100% used artifacts:

Output of Measure Killer after cleaning up the Sales report

Even so, sometimes that is not enough, because the data can still be used in non-important or non-optimized ways. In that case, it's crucial to know what's being used by each page, visual, measure, to know what could be removed. That's where "What if? analysis" can help.

What if I have a test page in my report which is hidden and not actually needed? I can run Measure Killer's "What if? analysis" in its "page" mode.

"Page" mode in the "What if? analysis" mode selection window

Output of the "Page" mode for the Sales report

In the output, we can see that the "T. Tip: Updates" page is exclusively using 44% of the size of our model. Deleting it could cut down the size of my model almost by half.

If we expand the "T. Tip: Updates" page, we can see all columns in use only there (and nowhere else).

Expanded dependencies of the "T. Tip: Updates" page

All the measures and columns that have a green checkmark in the "Potential savings" column are only used on this page in the Sales report.

Since the information this tooltip provides is not that useful to the report, it's a good idea to remove this page completely to free up space. Upon removing it and running MK again, we lower the report's size to 1.5 MB, almost half of the initial size:

Final size of the Sales report, after removing the "T. Tip: Updates" page

Conclusions

By using Measure Killer and "What if? analysis", it's possible to drastically lower the model size of a Power BI report. The same principle can be applied to more complex examples, like live reports connected to a semantic model in the Power BI Service.