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!

Leave a Reply