Power BI – Organizing your Measures

By: David Rohlfs

Introduction

Looking to build your Best Practice knowledge in Power BI? I do a lot of Power BI reports and something that almost always comes up when taking over a report is duplicated measures because of disorganization! In this blog you will learn why and how to organize your measures in Microsoft Power BI.

 

When to use it

While measures aren’t the most important aspect of a Power BI report usually, they can carry a lot of weight when you are going to pass the report onto someone else. Let me explain, if someone else creates a report and they let their measures land wherever they are at the time without any organization, you’re going to take a lot of time and likely aggravation to find the measures you need because they are not organized. To help fix this, we need to follow some best practices so that it is simple to navigate.

We can usually use two different methods to organize our measures; we can let the measure live on the main table it is connected to, or we can create a Measure Table that will essentially act as a folder to store all of our measures in just like your computer’s file navigation.

Letting a measure live on the main table it is from works very well on simple report models. Think of the star schema, if you only have one fact table it becomes very simple to look through all of your data to find the measure you need. But with a more complex model schema you could be looking at multiple fact tables and then it becomes an issue to hold a measure in one fact table while it may be working with both or all fact tables. This is where we want to start creating a Measure Table.

Measure Tables are my favorite way to organize measures because they work very similarly to your computers file system. You just create a blank empty table with a name then assign whatever measures you would like to that table.

 

Common Problems

Calling your Table Name

If you start to store measures outside of their main table, you need to call the query in your equations. For example, I created a measure that takes the sum of the sales revenue.
Total Sales = Sum([Revenue])

While this may work if the measure is stored in your Sales query, if you put it into a Measure Table, you will need to call the table name.

Total Sales = Sum(Sales[Revenue])

 

Walkthrough

I am going to show two things in this walkthrough. First, we will look at how to reassign the query your measure is stored in. Second, we will go through the process of creating a Measure Table.

To reassign the location your measure is stored in, first you need to select your measure from the data pane then reassign the Home Table up in the top left corner on the Measure Tools ribbon.

Now let’s create a Measure Table. On the home ribbon, select the “Enter Data” option.

This will put you into a pop-out where you can manually enter data to create a query in Power Query. Instead, we are going to leave the table blank and rename the table to be “Measures”. Then click Load.

Next, we need to go ahead and move at least 1 measure into this table. To do this you just select the measure, then in the Home Table in Measure Tools, select Measures.

Finally, we are going to delete Column 1 from the model. To do this, in the Data Pane > Measures > Column 1, click the three ellipses and select “Delete from model”.

Now, your Measures Table should have moved to the top of your data list and the icon for your query should be the measure icon now.

If you want to create labeled folders, all you need to do now is just rename your measure table and create more if you want to.

 

Conclusion

While organizing your measures seems simple, it is often overlooked. When you create a report, you start to have a mental map of where everything is in that report. But if you take a few months away from it or if you pass it onto someone else, the disorganized nature of measures can start to become an annoying issue. Using Measure tables or following best practice can not only make it easier for the report builder to find the measure but also allow easy migration to another report builder.

Comments