Measure Tables in Power BI - Excelerator BI

Measure Tables in Power BI

I often get asked about measure tables during my Power BI online training 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.

image

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 use measure tables.

  • 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

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.
  • 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.

clip_image002[4]

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?

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.

54 thoughts on “Measure Tables in Power BI”

  1. Anthony Robinson

    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?

  2. 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

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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

  10. 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

  11. 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.

      1. @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).

  12. 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.

      1. @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).

  13. 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.

  14. 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.

  15. 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.

  16. 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.

  17. I like using measures tables to keep things more organized and easier to work with – that’s my own personal preference

  18. I like using measures tables to keep things more organized and easier to work with – that’s my own personal preference

  19. 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…

  20. 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…

  21. 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?

    1. 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.

  22. 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?

    1. 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.

  23. Bryan Campbell

    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…

    1. 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.

  24. Bryan Campbell

    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…

    1. 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.

  25. 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.

  26. 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.

  27. Jason Presley

    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.

  28. Jason Presley

    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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top