Another advantage to Measure Tables

Last year I wrote a blog on the pros and cons of using a measure table in your Power BI model – you can check out that article here: To Measure Table or NOT to Measure Table? – Greyskull Analytics

Ultimately it comes down to personal choice, with neither option necessarily representing a right or wrong practice.

However, I recently came across a scenario that adds to the list of advantages of using a measure table, so I figured it was worth capturing for posterities sake. This was a real world use case.

The problem

The data model I was working on had already taken the approach of putting measures in the fact tables and wasn’t using a measure table. The issue came up because we wanted to use the same name for our measure as the name of the column in the source table. Imagine your data source has a fact table representing sales. There is a column called “Revenue” and you’d like to create a DAX measure SUM(‘Sales’[Revenue]) so this can be aggregated using an explicit measure, and you’d like to call this “Revenue” also.

The tabular model won’t allow you to create this measure in your fact table, as you’d now have 2 objects in your model with the same name, ‘Sales’[Revenue].

The solutions

You have various choices to overcome this:

• Give your measure a different name. You could prefix it with something like “Total Revenue” or “Sum of Revenue” but personally I’m not a fan of this. That’s not how business users speak, and it also starts to make the names of objects in your model more verbose. I’d rather keep naming conventions succinct. I also feel that in a tabular model, which is best suited to aggregating and summarising data, that the measures being a “total” or “sum” of something is implied so there shouldn’t be a need to explicitly call it out in the measure name.
• You could add a power query step to rename the source column. This is just a faff and doesn’t feel like the most efficient approach. I also prefer for the lineage of my data to be more intuitive than this. I’d prefer that the names of columns in my data model match the names of the columns in the source data and that the names of the measures represent the names of the columns in the model they are aggregating.

My preference

With a measure table though, no problem. The source column in the fact table remains as ‘Sales’[Revenue] and your new measure is ‘@Measures’[Revenue] (or whatever naming convention you deem appropriate for your measure table).

So perhaps an edge case, and probably not something that will sway data modellers who prefer to leave their measures on the fact table, but certainly one more reason to add to my list of why I will continue to use and advocate for measure tables.