Microsoft Power BI – Get Yourself a Measures Table

Share Button

As a manager of an infrastructure mainly based in Power BI, there are a few improvements that I would like to see in the tool in the future. This is my first post about it and I’ll start with the one that I think is the easiest to implement and would have a major impact on the industry: configurable automatic measures table.

Improvement #1 – It should be possible to configure the automatic creation of a measures table

Power BI is the result of a building block platform:

  • You get your data using “Transform Data” (Power Query) and you edit it
  • Then you model it using the Model view of the Power BI file
  • And after that, you create your measures and visualizations for the users

So what is missing?

Usually, newbie users spread measures all over the data model which makes maintenance harder than it should be. The worst of this phenomenon is that we have the Multidimensional experience and knowledge which we could apply to improve everybody’s life.

What Do I proposE?

Power BI should allow a user to configure the automatic creation of a measures table whenever a new model is created. This configuration should allow setting the default name for it, for instance, “Indicators” or “KPIs”. It could also allow creating automatically some relevant measures which would contain information about the model like “Version Date”, “Version #” or “Last Refresh Time”.

After the Time intelligence block, there should exist as “Measures Table” block

Why is this important?

The main problem is definitely maintenance but there is also a deeper conceptual understanding that is relevant. When developing a model, user experience for drag-n-drop has to be taken into consideration, therefore it’s simple to create a single measure table with several folders to hold the measures and hide the fact tables.

Fact tables should be hidden and all the measures in the same place for easy drag-n-drop experience

But wait, there is already a Measures Table

True. Internally, there is already a “Measures Table” which holds all the measures created in the model.

As a test, I created a “Test Measure” in my “Northwind” model inside the “Categories” table.

Tabular Editor – Test Measure definition

Then I opened Dax Studio and ran a DMV query. As you can see, the unique name is “[Measures].[Test Measure]” which in MDX means “Table = Measure; Member = Test Measure”.

What is the end game?

The objective is to simply affect automatically the “Measure Group Name” of a measure. There are already plenty of blog posts showing how to manually create this kind of table:

But there must be an automatic and defaut way to set this.

Let’s help stop spreading measures all over the models and have automatic Measures table created by default.