How to Define A Measure Table in Power BI Desktop

In this post I show you a simple trick to make your Power BI model more organised and more readable. I call it creating a “Measure Table”. Let me explain. The story is that I was working on a model with lots of tables. The database schema was NOT a proper star schema so there were a bunch of measures spread into lots of different tables. On top of that we’ve created lots of calculated measures with different home tables which made it really hard to find a particular measure or calculated measure. I thought, well, when it is that hard to find the calculated measures at development time how hard it could be for a customer to find, understand and use the measures we created. The visibility of the calculated measures could be an issue when we have lots of measures in lots of different tables. You will soon feel the issue in customer training sessions when you need to navigate between lots of different tables to find a calculated measure.

Consider you create a Power BI model with direct connect to a SSAS Multidimensional instance. You will immediately notice that all measure groups have a special calculation icon (Measure Group Icon in Power BI) rather than a normal table icon (Table icon in Power BI) which makes the measure groups more recognisable for the end users. For instance, you can easily find any calculated measure related to “Internet Sales” under the “Internet Sales” measure group.

Measure Groups in SSAS Multidimensional Dirct Connect

I know, we can search and find the measures very easily, but, our model would be more organised and more user friendly if we can put all measures in one or more tables which contain just related calculated measures and nothing else. For instance, we can create a measure table for time intelligence calculations and name it “Sales Time Intelligence Measures” and put all  calculated measures like “Sales YTD”, “Sales LYTD”, “Sales Period Over Period” on it. It will make your model nice and clean, easy to use and easy to learn for your customers. It will also help you to train your customers more easily.

In this article I’ll connect to a SQL Server instance and will use the famous Adventure Works database. I also show you how to get the job done in both “Import” and “DirectQuery” modes as there are some limitations applied to the DirectQuery mode which makes it harder to do what we want.

Lets start.

How It Works

The idea is adding a new table with just one column having just one value in that column. Then we need to hide that column. This makes the whole table get hidden as there is no columns to show. Then we use this table as “Home Table” for all related calculated measures. Remember, we do NOT hide the table, but, just the column. In this case Power BI Desktop recognises the table as a measure table so it uses the measure group icon (Power BI Measure Group Icon) for it.

Create a Measure Table in Import Mode

  • Open Power BI Desktop
  • Get data
  • Connect to a SQL Server Database (Adventure Works as a sample)

Power BI get data from SQL Server 01

  • Select a desired table (FactInternetSales in our case)
  • Click “Select Related Tables” button
  • Click “Load”

Power BI get data from SQL Server 02

  • Click “Import” then OK

Power BI Get data from SQL Server Import mode

  • Click “Enter Data” from Home tab

Power BI Enter Data

  • Enter a value for “Column1”
  • Enter a name for the table then click “Load”

Power BI Create Table

  • Expand the new table from the “Fields” pane then hide the “Column1” column

Power BI hide columns

  • This will hide the whole table as there is no visible columns to show
  • Click “New Measure” from “Modeling” tab from the ribbon to create a new calculated measure

Power BI new measure

  • Type a desired DAX expression to make the new calculated measure then press enter

Power BI new measure DAX

  • This will create the calculated measure in the first table in the “Fields” pane

Power BI new measure 02

  • Click on formula bar again to enable the measure properties
  • Click “Home Table” and pick the newly created table from the list

Power BI new measure change home table

  • As you can see the table appears on the “Fields” pane again with the calculated measure

Power BI Task pane

  • As you can see the tables still has the normal table icon, but, if you toggle right the fields pane then toggle left it the icon will be refreshed

Power BI Toggle Task pane

You can create more tables like “Resellers Sales” and so on then move the calculated measures to the corresponding tables. In some cases you can hide the whole fact tables if you have created all calculated measures you need or when your customer won’t need to see any measures from the fact tables.

Power BI Table of Measures

Create a Measure Table in DirectQuery Mode

Basically we need to do the same process, but, this time we want to connect to a SL Server database in DirectQuery mode. So to get data we do exactly the same things:

Open Power BI Desktop=> Get Data=> Connect to a SQL Server database and so on.

It is important that you enter the database name at the first step of getting data.

Power BI get data from SQL Server 02

But, you’ll immediately notice that the “Enter Data” button is disabled in DirectQuery mode.

Power BI Enter Data disabled in DirectQuery

It’s OK. There is always an alternative way to achieve the goal.

As I mentioned before, we need to enter the database name when getting data. This is important as we’ll reuse the connection in the next steps and if we haven’t enter the database name you’ll get the following message:

“Connecting to tables from more than one database is not supported in DirectQuery mode”

Power BI connectin to different datanases is disables in DirectQuery

OK, here is the trick.

After you connected to the SQL Server database in DirectQuery mode follow the steps below:

  • Click “Edit Queries” from the ribbon

Power BI Edit Query

  • Click “Recent Sources” then click the most recent one

Power BI reuse existing source

  • Select a table. It doesn’t matter which table you select as we’ll modify it during the next steps. Click OK

Power BI Navigator

  • This will add the new table in the “Queries” pane (in my sample it is “AdventureWorksDWBuildVersion”)

Power BI Query Editor

  • Click “Advanced Editor” from “Home” tab from the ribbon

Power BI Advanced Editor

Power BI Advanced Editor 02

  • All you need to do is to add a simple query like “[Query=”SELECT 1 AS NEW_COLUMN”]” to the source

Power BI Advanced Editor 03

  • Now you have to comment out/remove the next line as well as the output in the “in
  • Put “Source” in the “in” block
  • Do not forget to remove the comma “,” from the end of the “Source” line
  • Click “Done”

Power BI Advanced Editor 04

  • You should see a table with one column

Power BI new table in Query Editor

  • Rename the query then click “Close & Apply”

Power BI new table in Query Editor 02

OK, now we have a table with just one column. From here the rest of the process is just like what we’ve done before.

Power BI new table in Fields pane

  • Hide the “New_Column” column
  • Add a new measure then change the “Home Table” to newly created table
  • Toggle right and left the task pane and you’re done

Power BI Table of Measures 02

You might think

“Well… this was a bit long procedure. What if I want to create more than one measure table? Do I need to redo the whole process again and again?”

The answer is NO, you do NOT have to redo the process for each measure table you want to add to the model. All you need to do is to create a reference query from the query you already created.

To do so:

  • Click “Edit Query” from the ribbon to open “Query Editor”

Power BI Edit Queries 02

  • Right click on the query you created previously and click “Reference”

Power BI reference table

  • Rename the reference table then “Close & Apply”

Power BI reference table 02

  • From here you need to follow the same process as explained before

Power BI Table of Measures 03

  • All done!

7 thoughts on “How to Define A Measure Table in Power BI Desktop

  1. I have been struggling with this same issue for a while now but connected to an Analysis Services Tabular Cube via Direct Query.

    Sometimes my FACT tables have the Measure symbol and are at the top of the field list, sometimes they have the Table symbol and are in the middle of the field list. This is extraordinarily frustrating in a large data model having to look one place for some FACT tables and another place for the other FACT tables (is there a way to just alphabetize regardless of table type!?).

    I’ve looked extensively and both FACT tables (with Measure symbol and without) only have fields with a Calculator or a Sigma symbol. Some of the Sigma symbol fields are ID’s, not base or calculated measures but this is true of both. There are no TEXT or DATE or other fields. Any thoughts? I’ve been searching for an answer to this for a while now…

    I can attach screen shots if it helps.

    1. Hi Scott,

      Welcome to BIInsight.com.
      That’s right, if you have any visible columns in your FACT tables, when you connect from Power BI to your Tabular model in Connect Live mode, then the table shows up as a normal table and not a Measure table (aka measure groups in multidimensional).

      On the contrary, if you hide all the columns from the FACT table so that the only things left are some measures and KPIs then the table shows up as a Measure Table when connecting live from Power BI to the model.
      The screenshot below shows the exact same model as the above screenshot after I hid all the columns.

      Let me explain a bit more around the fields with Sigma symbol and those with Calculator symbol.
      The fields with Sigma symbol are called implicit measures. Implicit measures are columns of in tables containing numeric values. Implicit measures are those ones that you don’t create them. As implicit measures are table columns they’re unique in a table (you cannot have two columns with the same name).
      On the contrary, the fields showed with Calculator symbol are indeed the measures you created by writing DAX expressions. Those fields are called Explicit measures. Explicit measures are unique across the Model regardless of the table that holds the measure. So CANNOT have two explicit measures with the same name across the Model.
      So, if you’re predominantly using Power BI as your reporting tool of choice then you may consider hiding all columns in your FACT tables and only leave the Explicit Measures and KPIs visible.
      Here is a screenshot showing the same model shown in the previous screenshots, but I unhid just one ID column, with Sigma symbol.
      When you either close the “Fields” tab in Power BI or close and reopen the PBIX file, the “Internet Sales” will no longer show up as a Measure table.

      Last but not least, different reporting tools behave differently when connecting to a Tabular model. For instance, when you connect from Excel to the same Tabular model, with or without hiding the columns in FACT tables, it detects the all the measures and shows up those measures on top of the list under their corresponding table. If there are some columns in the FACT table, then Excel also shows that table as a dimension in the list.

      Hopefully that helps.
      Cheers.

  2. Soheil, thank you for the great explanation!

    I know this is not a Microsoft forum per se but this is problematic for scenarios where we want base measures coming from the Data Warehouse itself, not DAX. This is true in lots of scenarios for performance, ease of ETL maintainence, etc…

    Even if we bring those “base” measures into the cube, hide them, then create a DAX, we’re still not able to achieve the desired result explained above. At that point I don’t see that we would have any choice but to NEVER do a base measure in the ETL/Source…it would always have to be in DAX?

    Does that sound right?

    Any thoughts/ideas?

    1. Hi Scott,

      Not a problem at all, I’m happy that you chose to ask your question here indeed.
      To answer your question I have to say that Power BI creates temporary explicit measures whenever you put an implicit measure on a visual.
      The temporary explicit measures are bound to the visual you’re using, therefore when you use the same implicit measure in other visuals, Power BI creates another explicit measure dedicated to that visual which is NOT a good practice.
      It is indeed best practice to create explicit measures for all implicit ones and hide all the implicit measures in your model. Especially when you’re building an SSAS Tabular model that serves multiple reporting tools including Power BI.

      Cheers

  3. With Direct Query fact tables and a measure table as suggested, do the measures get updated when the DQ is done? I think they don’t, which makes visuals dependent on the measures not update. Is this accurate? If so, this would seem to be a reason to keep the measures in the DQ tables. Am I missing something? Thanks!

    1. Hello John,

      Welcome to BIInsight.com.
      The measures in measure tables are just like any other measures either in Direct Query mode or in Data Import mode.
      The difference however is about how frequently your model refreshes in Direct Query mode in the context of Power BI Service.
      I presume you’re using an on-premises database, therefore using On-premises Data Gateway is inevitable.
      When configuring the gateway in your Dataset settings in Power BI Service, you have the option to select how frequent your Dataset should be refreshed.
      The minimum data refresh in 15 minutes.
      Check this out for more details: https://docs.microsoft.com/en-us/power-bi/refresh-data.
      That said, when you interact with the visuals in your report, the Dataset fires concurrent queries back to your data source, therefore your data refreshes.
      Hence, if you do NOT touch the report, like when you project a dashboard/report on a screen and no-one manually interacts with the dashboard/report, the minimum frequency of your Dataset refresh takes over and refreshes your data automatically. Otherwise, whenever you interact with the visuals, your data gets refreshed.
      In Power BI Desktop however, it is not exactly like that.
      There is no automatic data refresh settings available for Power BI Desktop therefore you don’t see any changes in the data when the data in the data source changes unless you hit the refresh button or interact with the visuals.
      In some cases even when you interact with visuals, like changing values in slicers, you may notice that the data changes do not show in the report.
      The reason for that is that Power BI Desktop uses caching and shows cached data while the data in the data source is actually changed.
      So my suggestion is that if you are developing reports in Power BI Desktop hit the refresh button to make sure your data is up-to-date.
      For Power BI Service you shouldn’t have any problems if you configured the gateway correctly.

      Hopefully that helps.
      Cheers.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.