How to manage Measures in complex reports
Power BI allows to calculate KPI’s in many different ways. A very common approach is the use of dynamic calculations – so called Measures. This approach is cool and useful – but in more complex reports you will rapidly loose control on where to manage all your formulas if you have no concept.
This post gives you initial guidance for your start.
When reports become more complex the number of measures will rise, too. I worked with quite a number of BI Experts and it seems to be common practice to store the measures in the tables where the calculations are made.
At first glance, this might sound reasonable. However: in which table should we store the measure if it references many tables at the same time? This is actually quite often the case. So, in which table should we store the measure? Maybe in the facts table where the central data might be stored?
Well, I think it would be elegant to aggregate measures into one or more groups.
Unfortunately, Power BI Desktop does not allow us to create catalogs for measures with supportive UI yet.
Good practice approach
However, we can apply a workaround that will effectively allow us to separate measures from the remaining content.
We recommend to create an additional empty table and move the measures into that table.
- Create an empty table. Follow the mini-steps as on screenshot below:
- Choose Enter Data
- Provide the name for your new Table for storing the measures. (Tip: It is worth to precede the name of the table with _ or # to keep that table at the beginning of the list of tables in the Fields Pane)
- Load the Table
- Add new or move existing measures to the newly created table. Follow the mini-steps as on screenshot below:
- Select the Modeling Tab.
- Select the Measure you want to move.
- Choose the Home Table from the dropdown in the Properties section.
- Choose the #Measures table
- You can also remove the unused Column1 column.
- The table icon will change to the measure icon. This will visually distinguish the set of measures from other tables. Sometimes it requires a restart of your modeling environment.
In the above way, we can create many groups and assign measures according to different categories – depending on our needs. Thanks to this, our pbix file will become clearer and much easier to manage and measures will be easier to find. Thus, our work efficiency will increase.
I found already two ideas in the Power BI Community how to improve the Measures Management, but they still need more of our votes to succeed:
In the second Idea, the author DaveG provides an example of mockup how the measures editor feature could look like:
Vote for existing or provide your own ideas how to improve the Power BI.
Remember! Being a part of a community, you have an impact on upcoming changes in Power BI.
Many thanks to my colleague Łukasz Klimczuk for his support while creating this Blog Post.
Is this workaround useful for your work, too? Do you use it already in the same way in your Data Science projects?