Creating and maintaining a dashboard in Power BI can become an overwhelming task if we are not mindful of organized data modeling. Oftentimes when we work with a complex dataset, we create measures and custom calculations which are essential for the report. Measures are calculations used in data analysis that aggregate data dynamically. 

To read more on how to create measures, you can read this blog here on the Case When site.

Organizing the data model can be done in numerous ways. One of the most common practices which can help achieve this is by creating a separate table only for your measures in Power BI. 

In this blog, we will lay out the steps involved to get started with creating separate measures tables in Power BI!

What Exactly Is A Measures Table In Power BI?

A measures table is nothing more than a new table where we will store all the custom calculations which we create while building a new Power BI report. Oftentimes in Power BI reports or dashboards, you will see a plethora of measures scattered all throughout the data view in the right side panel like this:

This makes it very difficult to manage and use measures especially when the report becomes more and more complex in the long run. It can waste valuable time having to scroll through all the different tables trying to find the right measure for the visual. 

Instead, we can put all the measures in a new table which we can name the “Measures Table”!

How To Create A Measures Table

Here is how you can create a measures table from scratch:

• In Power BI Desktop, click on Enter Data from the Home tab of the ribbon on top. This will open a new window which will ask you to enter data manually.

• Rename the table as Measures Table or anything you deem appropriate and click on the green Load button.    

• Next, create a measure by right clicking on the newly created Measure Table and selecting “New Measure”.

• Create a measure - for this example we will just create a measure called Total Health Score. Consequently, delete “Column 1” which was created by default. Doing this will automatically change the table into the measures table, which is identified with a calculator icon instead of the table/Excel sheet icon for other tables.

• Create a measure - for this example we will just create a measure called Total Health Score. Consequently, delete “Column 1” which was created by default. Doing this will automatically change the table into the measures table, which is identified with a calculator icon instead of the table/Excel sheet icon for other tables.

• You have now successfully created a measures table and can start adding new measures to this table! 

That wasn’t too hard, right?

How To Create Folders In The Measures Table 

Similar to how we structure and maintain our folders and files in our PC, we can do that in the measures table in Power BI as well! 

This allows us to store, for example, total measures in one folder and average measures in a separate one for ease of maintenance. 

Here is how we can do that easily:

Click on the data model view on the left side panel

Select the measure you want to place in a subfolder and put the name of the subfolder in the display folder option in the properties panel. This creates a subfolder in the measures table.

You can create any number of subfolders using this method. This can be really useful to separate the total measures from the average measures as shown below:

The average measures folder is collapsed here. If needed, you can drag and drop any number of measures into this subfolder from the model view! 

Convenient, isn’t it?

In Closing

Creating a Measures Table in Power BI is a best practice that can significantly enhance the organization, readability, and manageability of your data model. By consolidating all measures into a single table, you simplify your model, making it easier to navigate, update, and understand.

This also greatly improves the life of the next person who works on your report!

So next time you are working on a Power BI report or dashboard, try following this best practice and see how it can help in collaboration and teamwork within your company.

To learn more such Power BI tips and tricks, read this blog to know about data modeling best practices in Power BI.

FAQs

Can I create more than one measures table in a Power BI data model? 

Yes, you can. But it is not necessary especially if you use subfolders as shown in the article here.

Do I need to create a relationship for my measures table with any of the other fact or dimension tables in my data model?

No, your measures table can compute any measure from any table and store them all in the same table. 

What is DAX?

Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI. Some examples of DAX are the Sum, Sumx, Calculate functions.