This article was originally written by Spencer Baucke and updated for 2023 by Gavin Pedersen.
Letting your Power BI data model get disorganized is a feeling every Power BI developer knows all too well, and it’s something that can occur very quickly when developing.Â
There are lots of ways to organize your data model so that it doesn’t have to feel like you’re getting lost in a sea of tables and fields whenever you open it. One of the most common ways to organize the data model is to create a separate table exclusively for your measures.
In this blog, we’ll provide steps on how to quickly create a measures table in Power BI with a few short steps.
What’s a Measures Table From Power BI?
A measures table is nothing more than a separate table in which all of your measures will be stored.
Implementing a measures table is useful because it not only keeps the data model tidy but it makes it easier to find some of the most frequently used fields from your field list by separating them out into their own distinct area.
How to Create a Measures Table
To create a measures table, follow the simple steps below:
1. With Power BI Desktop open, click Enter Data on the Home tab of the ribbon. A new screen will appear in which you can manually enter data (or copy and paste it from Excel, but we won’t use that functionality here).
2. Rename this new table Measure Table and then click on the green Load button. You will now see this table in the field list on the right.
3. Create a measure in the Measure Table – we’ll use Total Profit for our example.
4. Delete the default column Column1Â – this will automatically change your table into the measures table, which is donated with a calculator icon instead of the table/Excel sheet icon for other tables.
5. You are now free to start adding all of your other measures to this newly created measures table.
Create Folders in the Table
If you have lots of measures and want to take the organization of this measures table that you just created to the next level, create folders for your measures.
Follow the steps below, where I create one folder for measures that total values and another folder for measures that average values.
1. Open the model view by clicking the data model icon on the left.
2. Select one of the measures from the field list and enter a name for the Display folder in the Properties panel (we’re calling ours Total Measures)
3. Do this for any other folders you may want to create and simply drag the measures from the field list into their respective folders (we created Average Measures as another folder and then collapsed it in the screenshot below).
Closing
Although we consider using a measures table to be a best practice, it’s easy to understand why people wouldn’t want one.
Sure, it’s a few extra steps on the front end, but it adds simplicity and organization to your data model, which will in turn create efficiencies in your development.
FAQs
Sure you can! But you don’t need to, especially if you create folders for your measures.
Nope, your measures table can calculate any measure from any of the tables and place them in the same table. In fact, it’s impossible to create a relationship with your measures table.
