How to Create a Measure Group/Table in Power BI

When you have a very large dataset inside Power BI with many tables, it can become hard for a user to find measures or see an overview of all measures at once. Consider the below example, how can a new user know which tables contain measures without opening each one of them.

A very user easy solution exists, we can create a Measure Group for measures. The result will look like this:

Creating the Measure Group

Measure Group with Imported Data Model

Step 1: To create a Measure Group for the Non-Direct Query model or Composite Model, you need to add a new table using the Enter Data option on the Ribbon.

Step 2: Enter the name of the Measure Group you would like to create.

Step 3: The next step is to click on your calculated measure(s) you would like to place inside the Measure Group, and change the Home Table property to your newly created Measure Group.

Step 4: You should now be able to see your calculated measures inside the new Measure Group. Now you can delete the fake column inside the Measure Group which you created in Step 2.

Step 5: Once you have deleted the column and the group only contains measures, you can toggle the FIELDS pane.

It is as easy as that to create a Measure Group.

Measure Group with Direct Query Model

When using Direct Query creating a Measure Group requires a different approach, because the Enter Data option is not available in Direct Query, unless you are using the new Composite Models feature in Power BI.

Step 1: Click the Edit Queries option in the Ribbon.

Step 2: In order to create a Measure Group we need to use the exact same data connection which is used in the Direct Query data source. Therefore, under the Queries pane, right click on any small table and click Duplicate. 

Step 3: Once you have Duplicated your table, right click on the table and open advanced editor.

Step 4: Replace the M code with the below-simplified code to select only 1 row and 1 column.

Note: This is an example for SQL Server data source

Step 5: Rename the table now to your desired Measure Group name. The final result will be like this.

Step 6: Save & Apply the changes. The next step is to click on your calculated measure(s) you would like to place inside the Measure Group, and change the Home Table property to your newly created Measure Group.

Step 7: Hide the fake column & toggle the FIELDS pane.

Now you can satisfy your users with a better look and feel when interacting with the model.

You may also like...

3 Responses

  1. Betty says:

    Thank you for sharing.

  2. Tomasz Gacek says:

    Well done! Thx!

Leave a Reply

Your email address will not be published. Required fields are marked *