Using a measures table in Power BI is generally considered best practice, and there are certain advantages to using one. However, I’ve found in my experience that the negatives outweigh the positives and that measure tables simply aren’t that intuitive.
In Power BI, measure tables are used to contain all of a datasets measures in a single table. This table contains no columns and simply contains the measures that you’ve created in your report. The thought is that this is an easy way to group the measures that have been created in a central area, and can be pinned at the top of the tables fly-out.
Measure tables even get delineated differently from normal Power BI tables, as you can see from the image below:
Disadvantages of Measures Tables in Power BI
My biggest gripe with using a measures tables in Power BI is that they simply aren’t that intuitive. In a large data-set with many different tables, I find it way easier to keep the measures in the tables that contain the columns they were built on. For example, if you have a measure calculating the sum of the total invoices for the year, its much more intuitive to keep it in the Payments or Invoices table rather than a generic measures table. In reports focused on smaller amounts of tables this could work out well, but its very hard to scale. I find myself having to re-read the DAX each time I’m looking for a measure to understand what columns and tables are used. Some of this can be solved using a good naming convention, but if you have hundreds of measures even a good naming convention can get overwhelmed.
Another disadvantage of using a Measures table that I’ve noticed is simply that its slower. This seems to be because Power BI does a more efficient job of storing a measure in a conventional table rather than a measures table. An easy to check this is to create the same measure in a conventional table and in a measures table, and use DAX Studio to view the metrics. From what I’ve seen, the measures in a measure table tend to be significantly larger.
Summary
Some people do like to use measures table as a way to keep all of their measures in one place. This approach works for many developers, but in my experience is easier to keep the measures in their respective tables. It allows you to intuitively know where your measures are stored based on their function and what columns they use.
For better ways to organize your data model that don’t include measures tables check out our blog post on Fact and Dimension tables in Power BI.
0 Comments