To Measure Table or NOT to Measure Table?

Who’d have thought it could be such a controversial topic?! The humble measure table.

In one of her recent videos (that wasn’t even anything to do with measure tables) Ruth from Curbal made a throw away comment about not being a fan of measure tables (skip to 8:40 on this video)

It resulted in some interesting discussions on Twitter about the pros and cons of measure tables and whether or not they should be considered best practice and it felt it was a subject worth capturing my thoughts on in a blog.

What is a measure table?

So what even is a measure table? In fact when I first came across the idea the team I worked in referred to them as a “measure stub”. It’s a technique in Power BI (and SSAS Tabular models before that) whereby you create a table that displays no data, and then use this as the table to create all of your measures against.

There are a few ways you can achieve this. If you take any table in your data model and hide any field that ISN’T a measure it will become a measure table automatically. Or you can create a dedicated measure table from scratch either by using M (I do it using the ‘Enter Data’ feature), you can use a DAX calculated table or god forbid I came across someone who once used a SQL view to do it.

Why do measure tables exist?

Why does this concept exist? 

One possible interpretation of the measure table origin story involves mentioning the T word… Tableau. I’ve had some limited exposure to working with Power BI’s arch-nemesis. I could bemoan it’s lack of decent data modelling capability, or I could celebrate it’s more intuitive visualisation features, but one quite neat thing with it is that by default it sorts dimensional attributes for slicing and dicing, and then numeric numbers to be aggregated into 2 separate categories. It even gives them some nifty colour coding to help differentiate the types of object further. So a measure table goes some way to mimicking this feature.

Or perhaps measure tables are another example of fuddy-duddy SSAS multidimensional developers clinging to the past. That’s just how things worked in multi-dimensional land. You created a set of dimensions and your measures had to reside in a measure group, which was a separate construct. These measure groups presented themselves as a separate entity when users connected to an old style OLAP cube using Excel. 

Even in modern Power BI world, if you leave your measures on a fact table, you’ll still end up with them migrating to their own separate folder once you connect using either the Analyze in Excel feature or if you’re using the query builder in paginated reports (both of which have MDX heritage).

Now I’ve complained about the old school multi-d folks in the past and my annoyance at their stubborn attitudes to embracing the Tabular Model… but in this instance I’ve always tended to let them off the hook, ‘cos I quite like measure tables.

The advantages of a measure table

Here’s why I think measure tables are a good thing:

• They keep measures organised. With a measure table you always know which table a measure is going to be in.
• If your measure traverses fact tables, say you’re comparing your sales table with your budget table to calculate a variance, where should that measure live? The sales table or the budget table? Well with a measure table, you don’t have to worry about making that decision. 
• A measure table will automatically display a different icon to dimension tables, helping to differentiate it from data that is used for slicing and dicing. It will also automatically sort the table to the first entry in the fields pane. This is a visual aid for self service users that I think helps them recognise that measures and dimensional attributes serve different purposes in a data model.
• I like the fact that a measure table gives a consistent user experience across Power BI data model access methods. Whether you’re in Power BI desktop, using the Power BI service, Analyze in Excel or the Paginated Report builder the data model will be presented in a consistent manner, making it easier for users to find what they need.
• They encourage the use of explicit measures. By their very nature, an implicit measure can’t exist on a measure table. You have to explicitly write the DAX code for a measure to be included. 

Now Ruth from Curbal’s case against measure tables is that they are unorganised and confusing for users. Certainly I could support that view if you dump all of the measures into one long list, but as long as you use display folders to organise the measures into an intuitive means of navigating them, I think that is negated. Ruth remains unconvinced though and she still believes that leaving the measures on the fact table is easier for people to understand. I guess usability can be a pretty subjective thing, so we’re probably not going to see eye to eye on this.

Reasons why you might not use a measure table

However, that’s not to say that measure tables aren’t without their flaws, and if it is a feature you plan to adopt, it’s important that you understand their limitations.

Firstly, when using Analyze in Excel, you will lose the drill to details feature in pivot tables. This kind of makes sense, as your measure table doesn’t have any physical relationship to the rest of your data model and the table itself has no detailed data to display.

Another oft cited drawback is that measure tables don’t work with Q&A. This is a fallacy, or at least it is now. The Microsoft documentation states the following:

However, almost like the cartoon character trying to plug leaks in a sinking ship, as one is covered up another one springs up.

Whilst I haven’t experienced it myself, as it’s a feature I’m still yet to explore in any real depth, I’ve heard reports that measure tables do no play well with the new Composite Model/Meta Model/DirectQuery-Over-Power-BI-Datasets-and-Azure-Analysis-Services feature.

Now the new DQOPBIDAAAS feature (such a catchy name) is still in preview, so whether these anecdotal issues with measure tables are regarded as a fault or a feature remains to be seen. Perhaps, as with Q&A before it, the engineering team at Microsoft will find a way to overcome the problem. However, even if that is the case, it’s fair to say that whilst measure tables seem to be a widely adopted practice amongst Power BI professionals, support for them does not appear to be at the forefront of minds when new features are being developed. If you want to stay at the bleeding edge of new feature adoption, this could be another good reason to avoid measure tables.

Choose your side

I’m happy to state that I am pro measure table, and I plan to continue using them in my data models. However, I want to stress that I don’t consider that NOT using them is bad practice. There are some corners of the Power BI community that quite vocally insist a measure table is a MUST. In my eyes, whether you use them or not is personal preference.

What I think is more important is understanding what they are, why they exist and how they work. It’s down to you to decide whether they make sense for you and your data models.

So, which camp are you in?