I often get asked about how to create measure tables during my Power BI course classes. This post will discuss the merits of measure tables versus storing your measures in the table where the data comes from.
What is a Measure Table?
A measure table is a dedicated table used to store some or all of the measures in a workbook. Measure tables only contain measures (no columns). When you setup a table up this way, the table icon changes as shown below.
Note: after you first delete (or hide) all the columns in the table, the icon does not immediately change to the calculator. To trigger the change, you either must shut the workbook and reopen it, or better still collapse the side FIELDS panel and then expand it again. After the calculator icon is displayed, the measure table will automatically float to the top of the table list.
Why Use Measure Tables?
There are various reasons why some people like to create a measure table in Power BI.
- It is possible to have the table appear at the top of the tables list (measure tables with a calculator icon float to the top of the list)
- It can make it easier for end users to find the measures, all in one location
- When measures are placed in the table where the data comes from they are sorted alphabetically, intermingled with the columns and hence are interspersed throughout the table. You can overcome this issue by using a sub folder.
To create a sub folder (in a main table or a measure table):
- Go to the Model view
- Click the ellipsis in the top right of the table, select Measures
- Go to Properties\Display Folder and type a name of a folder
- You can also create nested sub folders as MainFolder\SubFolder
Possible Reasons Not to Use Measure Tables
There are a few reasons why you may choose not to use a measure table.
- Power BI contains a concept called question and answer (Q&A). The artificial intelligence algorithms used in this feature consider which table the measure is located In order to improve the results from Q&A. If you move the measures into a different table, the quality of the Q&A result may be impacted. Note however I have not noticed any difference when using measure tables.
- It may be more intuitive for a user to look for a measure in the appropriate table. Eg Total Sales in the Sales table, Total Inventory in the Inventory Table, Total Customers in the Customers table. (Note: creating measure sub folders in a table can solve the sorting issue).
How to Create a Measure Table in Power BI
The generic pattern to follow is to:
- Create a table with a single column.
- Add at least one measure to the table. There are 2 ways to do this.
- Method 1: Move measures individually
- Right click on an individual measure
- Go to Modeling\Home Table and change the location to the Measure table
- Method 2: With the new model view in Power BI Desktop, it is now possible to drag and drop measures between tables. Click on the link to watch my YouTube video about that.
- Method 1: Move measures individually
- Hide or delete the column leaving the table showing measures only. (I prefer to hide)
- Force the measure icon for the table (collapse and expand the fields panel on the right).
There are various ways to create the measure table itself.
Option 1: The Seamark Method
This week I saw a Tweet by Phil Seamark showing a great new way to create a measure table. In short, you
- Click on Modeling\New Table
- Write the following DAX table function (note the space before the word Measures). This makes the table float to the top of the list.
[ Measures] = {Blank()}
One downside of doing it this way is the table icon doesn’t change when you hide the only column. I am not sure why this is – it may be a ‘bug’.
Option 2: Enter Data
The way I normally create a measures table is to use the Enter Data option.
- Home\Enter Data
- Don’t add any data to the table
- Rename the table _Measures (note the underscore). This is required as Measures is a reserved word.
Power Pivot for Excel
The easiest way to do it in Power Pivot for excel is to copy a single blank cell from an Excel spreadsheet and paste it into the Power Pivot window (see 1 below). Simply give the table a name and click OK.
Alternatives to Using Measure Tables
There are some options instead of using measure tables.
- It is possible to create sub folders for measures using the latest versions of Power BI desktop. You can make sub folders float to the top of the table using a suitable name for the folder (ie it will sort alphabetically).
- It is also possible to hide all columns in a data table just leaving the measures visible. When you do this the data table behaves like a measure table anyway. There are some downsides when you hide all the columns
- You remove the possibility to drag and drop the column itself (eg to find the MAX(Sales[Quantity]) without writing a measure).
- You may need one of the columns for analysis, eg Invoice Number.
What do You Think?
What do others think? Do you have different better ways to do this, or do you have a different reason for using measure tables?
Don’t forget to check out my power bi course where you can learn more about this topic and other Microsoft Power BI functions.
How about using Column Sub Folders?
After writing this article, I found that grouping columns into a sub folder is a better alternative than using measure tables. You can read the article here.
Hi Matt,
how could I achieve this when I connect PB to Analisys Services tabular?
I think with the new composite models feature from Dec 2020, this should now be possible. https://exceleratorbi.com.au/the-most-important-update-to-power-bi-desktop-ever/
Previously you would need to get the AS author to add a blank table for you.
Matt,
Thx for taking the time to share.
Hi Matt,
Your post was helpful in keeping the measures separately at a place.
But I am not able to get the calculator icon before the table, even when I have created it using the “Enter Data” feature.
I have hide the column and now there are only two measures, but an icon of normal table.
Awaiting your response…
– Varun
Hi Varun,
I was able to create two measure tables using the “Enter Data” method. The first, I deleted the column. The second, I hid the column. In both cases, once you “Show/hide” the Fields pane the calculator represented the measures table.
– Dale
Interesting. So just to confirm, you have a measure table created using “enter data” and you have no visible columns. Did you then collapse and reopen the fields list (or shut down/reopen the workbook)? That is also needed to make it appear the first time. What version of Power BI are you using?
Topical measure tables appears the way that I am going to setup my future models. The need to separate the concerns of the objects that re.side within a container (table). AS dimensional and fact tables should have only have columns whereas measure table should only maintain topical measures. It is easier to find measures for reuse by topic.
Thanks for all of your great articles
I’ve created a measure table and followed all the usual steps, but for some reason, Power BI refuses to recognize the table as a measure table, and still displays the normal “table” icon versus the calculator icon.
Even tried to “force” it and nothing changes. The measures a re correct and I’ve hidden the Column1 from view, but still doesn’t work.
Any tips on this?
How did you create the table? I believe that if you create the table using the new table button, it won’t work. You need to create a table using “enter data”
Hi Anthony,
What about hiding the Fields pane and showing it again?
Regards,
Fernando
Hi,
Somehow when I created new measures in a table, it’s not ‘grouped’ in the Measures Tables. Please advise on how can this be resolved.
Tks: -Nik
That is how it works. Read my article here https://exceleratorbi.com.au/column-sub-folders-better-than-measure-sub-folders/
Personally, I prefer to create a Measure folder for each Table (Sales, Customers, Calendar etc), so I group or displayed the measures into the folder created. Not sure if it is going to affect the AI algorithm for Q&A though.
Personally, I prefer to create a Measure folder for each Table (Sales, Customers, Calendar etc), so I group or displayed the measures into the folder created. Not sure if it is going to affect the AI algorithm for Q&A though.
@Uriel OMG! Exactly what I needed. Thanks 🙂
@Uriel OMG! Exactly what I needed. Thanks 🙂
Personally, since I discovered the possibility of creating a table of measures, I do it systematically. It’s mainly for organizational reasons like finding all my measurements more easily. In addition, previously, I often happened to create a measure in the wrong table and have to correct it, this is no longer the case.
Personally, since I discovered the possibility of creating a table of measures, I do it systematically. It’s mainly for organizational reasons like finding all my measurements more easily. In addition, previously, I often happened to create a measure in the wrong table and have to correct it, this is no longer the case.
In terms of measure tables in Power Pivot and losing drill through detail, I vaguely recall reading something in a comment on a blog years ago (maybe Ken Puls’ ?) that a workaround was to make your measures table just a single row/column with 1 in it (rather than an empty table) and then add a column with 1 to all your source data tables and create a relationship between the measures table and all your source tables.
In terms of measure tables in Power Pivot and losing drill through detail, I vaguely recall reading something in a comment on a blog years ago (maybe Ken Puls’ ?) that a workaround was to make your measures table just a single row/column with 1 in it (rather than an empty table) and then add a column with 1 to all your source data tables and create a relationship between the measures table and all your source tables.
Matt
Thanks for the tip regarding Sort order. I never realized it was there.
Still It would help it there was a third option to sort the measures manually, as one wants to
Matt
Thanks for the tip regarding Sort order. I never realized it was there.
Still It would help it there was a third option to sort the measures manually, as one wants to
Sam
In replay to your question about maximum 1000 rows to drill Through:
No need to go “under the hood”, It’s quite easy to get more rows:
In the Excel menu:
Data / Queries & Connections.
A side bar titled “Queries & Connections” will appear.
Right click on the connection named “ThisWorkbookDataModle.
Select Properties.
You will see towards the end of the form an option to change Maximum records form 1000, to whatever you want.
@Uriell – Thanks !!! – I could I have missed this !
@Urial Strange, I’m not seeing the ThisWorkbookDataModule connection listed for the two files that I’ve looked at. (They both use PowerQuery to feed data into PowerPivot).
Sam
In replay to your question about maximum 1000 rows to drill Through:
No need to go “under the hood”, It’s quite easy to get more rows:
In the Excel menu:
Data / Queries & Connections.
A side bar titled “Queries & Connections” will appear.
Right click on the connection named “ThisWorkbookDataModle.
Select Properties.
You will see towards the end of the form an option to change Maximum records form 1000, to whatever you want.
@Uriell – Thanks !!! – I could I have missed this !
@Urial Strange, I’m not seeing the ThisWorkbookDataModule connection listed for the two files that I’ve looked at. (They both use PowerQuery to feed data into PowerPivot).
You don’t need to have just one measures table. I use multiple ones to group measures by theme. You can give these measures tables more meaningful names, like Measures_Sales, which helps with that issue of helping people know where to look for the right measure.
You don’t need to have just one measures table. I use multiple ones to group measures by theme. You can give these measures tables more meaningful names, like Measures_Sales, which helps with that issue of helping people know where to look for the right measure.
Have been using a measure table for a long time. I call it as M
Helps keep all the measures together and organised.
The only filp side is you loose drill down in a Pivot when you double click on a number.
But with Power Pivot only showing the top 1000 rows double click to get the raw data is not a great option any more.
Any one knows of a registry key to change to increase / remove this limit ?
I know we can create a parametric DAX Query to show the data on double click but I wish they had given us a setting where we can decide the drill down row limit.
Have been using a measure table for a long time. I call it as M
Helps keep all the measures together and organised.
The only filp side is you loose drill down in a Pivot when you double click on a number.
But with Power Pivot only showing the top 1000 rows double click to get the raw data is not a great option any more.
Any one knows of a registry key to change to increase / remove this limit ?
I know we can create a parametric DAX Query to show the data on double click but I wish they had given us a setting where we can decide the drill down row limit.
I like using measures tables to keep things more organized and easier to work with – that’s my own personal preference
I like using measures tables to keep things more organized and easier to work with – that’s my own personal preference
I’ve been working with Measure Tables for a long time in PowerPivot on SharePoint. We have a big FactSales which are separated into the Measures for the various kinds of sales data in the Measure Tables.
A nice twist I just discovered recently: you can even create relationships from blank fields to any linked Dimension Tables. These will then show in the PivotTable Fields in Excel when you select the Measure Table in “Show Fields”. This makes it much easier for the user to see the Dimensions related to the Measures in the Measure Table.
It does not help in Power BI, though…
I’ve been working with Measure Tables for a long time in PowerPivot on SharePoint. We have a big FactSales which are separated into the Measures for the various kinds of sales data in the Measure Tables.
A nice twist I just discovered recently: you can even create relationships from blank fields to any linked Dimension Tables. These will then show in the PivotTable Fields in Excel when you select the Measure Table in “Show Fields”. This makes it much easier for the user to see the Dimensions related to the Measures in the Measure Table.
It does not help in Power BI, though…
HI Matt
I myself can’t decide what is better. however I see 2 extra points related to this topic:
The biggest reason I see not to move measures to a measures table is that in Excel, when you double click a Value Cell (or right click on it and select Show Details), it opens an new sheet with the rows form the table related to that cell.
If you move the measure to a blank table, you lose this option.
Anther related issue is how to sort the Measures inside one long list. suppose I want them sorted according to some categories and not by default (in excel by default they do not appear in alphabetical order, but in the order you wrote them).
What’s your thoughts about that?
I didn’t know about the first point – I will test it. Regarding the second point, by default the measures are in the order they were written. But you can change this to sort alpha from the cog menu above the fields list.
HI Matt
I myself can’t decide what is better. however I see 2 extra points related to this topic:
The biggest reason I see not to move measures to a measures table is that in Excel, when you double click a Value Cell (or right click on it and select Show Details), it opens an new sheet with the rows form the table related to that cell.
If you move the measure to a blank table, you lose this option.
Anther related issue is how to sort the Measures inside one long list. suppose I want them sorted according to some categories and not by default (in excel by default they do not appear in alphabetical order, but in the order you wrote them).
What’s your thoughts about that?
I didn’t know about the first point – I will test it. Regarding the second point, by default the measures are in the order they were written. But you can change this to sort alpha from the cog menu above the fields list.
You can do this in power query with a blank query
= {1}
Convert the list to table.
Then load to the model…
Then add a measure to this new table.
Then hide the non measure column
Then hide and show table list in PBI desktop
Now u sort to top, have the special symbol for metric table, can copy and paste the m code to other pbix…
But I was unaware of the affect on q and a…
That’s the way I’ve done it. You don’t need the = {1} though; you can just leave the query blank.
I use Measure tables for when I have measures that reference 2 or more other measures, which themselves are in different tables. Those ones don’t typically ‘belong’ anywhere, so I just put them in their own Measure table.
I had it in my head that measure tables offered some kind of performance improvement but they’re actually just a method of managing and maintaining measures in an efficient and orderly way.
I’ve recently created a number of columns that I quickly lost track of so I began prefixing them with an * so that when I type * in the search bar all the new columns are displayed. The same could be done with measures quite easily which would achieve the same result as a measures table.
Hth,
Eddie
You can do this in power query with a blank query
= {1}
Convert the list to table.
Then load to the model…
Then add a measure to this new table.
Then hide the non measure column
Then hide and show table list in PBI desktop
Now u sort to top, have the special symbol for metric table, can copy and paste the m code to other pbix…
But I was unaware of the affect on q and a…
That’s the way I’ve done it. You don’t need the = {1} though; you can just leave the query blank.
I use Measure tables for when I have measures that reference 2 or more other measures, which themselves are in different tables. Those ones don’t typically ‘belong’ anywhere, so I just put them in their own Measure table.
Matt, my experience is the same with Option 1. The icon does not change. I left a comment on Phil’s site.
Yes, I saw your comment. At the time I thought you just didn’t know how to make it appear. It was only when I wrote this blog that I realised it didn’t work.
Matt, my experience is the same with Option 1. The icon does not change. I left a comment on Phil’s site.
Yes, I saw your comment. At the time I thought you just didn’t know how to make it appear. It was only when I wrote this blog that I realised it didn’t work.
Ken, I’m finding this as well, especially as my users are using Analyze in Excel.
Ken, I’m finding this as well, especially as my users are using Analyze in Excel.
Personally, I don’t believe in Measure Tables any more. (They were useful when Excel’s Power Pivot was super unstable and there was the threat of having to re-create your main tables, but I don’t see that much any more at all.) I’m a big fan of hiding all foreign keys as well as all un-aggregated columns in a table, leaving only my measures visible. This has the same effect of popping them to the top of the list, but leaves them “classified” by the table name. And if I need one of those columns for analysis… it should be on a dimensional table anyway.
I agree Ken, however as I said in the post, if you have (say) an Invoice Number in the Sales table that you want to keep, you can’t create a measures table.
Personally, I don’t believe in Measure Tables any more. (They were useful when Excel’s Power Pivot was super unstable and there was the threat of having to re-create your main tables, but I don’t see that much any more at all.) I’m a big fan of hiding all foreign keys as well as all un-aggregated columns in a table, leaving only my measures visible. This has the same effect of popping them to the top of the list, but leaves them “classified” by the table name. And if I need one of those columns for analysis… it should be on a dimensional table anyway.
I agree Ken, however as I said in the post, if you have (say) an Invoice Number in the Sales table that you want to keep, you can’t create a measures table.
I think the new folder capability has largely made the need for Measure Tables redundant. However, Jason’s point is a good one.
I think the new folder capability has largely made the need for Measure Tables redundant. However, Jason’s point is a good one.
One need for measure tables I have, and this is probably a limited use case, is to use Object Level Security to secure measures from certain customers. That way I can put the restricted measures into a measure table then secure that table using OLS for specific roles.
OLS? Is this a SSAS feature? I haven’t heard of it in Power BI Desktop
That’s a good point Matt. OLS is only in Analysis Services, at least for now.
One need for measure tables I have, and this is probably a limited use case, is to use Object Level Security to secure measures from certain customers. That way I can put the restricted measures into a measure table then secure that table using OLS for specific roles.
OLS? Is this a SSAS feature? I haven’t heard of it in Power BI Desktop
That’s a good point Matt. OLS is only in Analysis Services, at least for now.