A measure table called “Measures”

I wrote a blog back in 2017 on how to better organise your measures in Power BI. One thing that bothered me at the time was I wasn’t able to give the measure table the one name I truly wanted.

The perfect name for a measure table is, of course, Measures. Unfortunately, this is a reserved word and gets blocked, so I always ended up calling my measure table something like All Measures, My Measures or !Measures.

These table names work OK, but I wanted to use the name Measures. Well, today, like all useful discoveries, I accidentally stumbled on a method that allows me to name the table the way I originally wanted.

The trick is to place square parenthesis around the name of the table, but include a space before the table name, as follows:

[ Measures] = {BLANK()}

Adding a measure table called Measures

In Power BI Desktop, use the New Table option on the Modeling tab and post the above code. This action will create a new dummy table with a single column/single row. This column can get hidden, so the only objects that appear against the Measures table are calculated measures.

Note the tidy syntax to create a minimalist table using the {} brackets. This approach is a syntax I find myself using more and more in tools like DAX Studio when running quickfire queries against a model, as an efficient way of generating the table output required.

EVALUATE {COUNTROWS('Sales')}

The bonus of using the space character as a prefix is it places the new table at the top of the field list. I did wonder if this approach might mean the table name in the field list would no longer be vertically left-aligned with the other table-names, but luckily the Power BI Desktop UI strips the leading space out to appease those with severe cases of OCD.

Once you have this table in your model, you can use it to host all your measures, including using sub-folders. The fully qualified name for these measures will include the space, e.g. ‘ Measures'[Sum of Sales], but given the number #1 crime in DAX is to add the table-name prefix when referencing measures in code, this isn’t going to be a problem. Right? 🙂

5 5 votes
Article Rating

Leave a Reply

9 Comments
Inline Feedbacks
View all comments
zapppsr
4 years ago

If you hide the only dummy column, and leave only measures, it really becomes a Measure table, gets the Calculator Icon and goes up at the top of all tables, what is even more useful.

Kirill Perian
4 years ago

For some reason, I am not able to delete the dummy column, even after moving existing measures into the table. If I only hide it, the “Measures” tables does come up to the top of the list, but it does not get the calculator icon. Anyone else with the similar experience?

zapppsr
4 years ago
Reply to  Kirill Perian

You don’t have to delete the dummy column. You just need to hide it. For my experience, you will only see the calculator icon if there are only visible measures in the Measure table. For me, I have to close and reopen the file in order for it to appear. Some people reported just collapsing and opening the field pane will do. Good luck!

Kirill Perian
4 years ago

When I hide the dummy column and leave only measures, it does move the table to the top of the list, but it doesn’t get the Calculator icon. It does get the icon when the dummy column is deleted, but I am not able to delete the dummy column for a table created in this way.

Matt Allington
4 years ago
Reply to  Kirill Perian

Collapse the fields list pane, then expand it again. It will be a calculator

Matt Allington
4 years ago

Collapse the fields list, then expand it again. The calculator icon will appear

Gvg
Gvg
4 years ago

Been using name _Measures for years without any problem … But cool idea anyway.

trackback

[…] If you have tried a table named Measures, Power BI doesn’t like that and won’t allow it. But you can. Let me first give the credit to Phil Seamark, I learned this trick on his blog […]

trackback

[…] Phil Seamark on DAX […]