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 (
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 (
Create a Measure Table in Import Mode
- Open Power BI Desktop
- Get data
- Connect to a SQL Server Database (Adventure Works as a sample)
- Select a desired table (FactInternetSales in our case)
- Click “Select Related Tables” button
- Click “Load”
- Click “Import” then OK
- Click “Enter Data” from Home tab
- Enter a value for “Column1”
- Enter a name for the table then click “Load”
- Expand the new table from the “Fields” pane then hide the “Column1” column
- 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
- Type a desired DAX expression to make the new calculated measure then press enter
- This will create the calculated measure in the first table in the “Fields” pane
- Click on formula bar again to enable the measure properties
- Click “Home Table” and pick the newly created table from the list
- As you can see the table appears on the “Fields” pane again with the calculated measure
- 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
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.
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.
But, you’ll immediately notice that the “Enter Data” button is disabled in DirectQuery mode.
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”
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
- Click “Recent Sources” then click the most recent one
- Select a table. It doesn’t matter which table you select as we’ll modify it during the next steps. Click OK
- This will add the new table in the “Queries” pane (in my sample it is “AdventureWorksDWBuildVersion”)
- Click “Advanced Editor” from “Home” tab from the ribbon
- All you need to do is to add a simple query like “[Query=”SELECT 1 AS NEW_COLUMN”]” to the source
- 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”
- You should see a table with one column
- Rename the query then click “Close & Apply”
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.
- 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
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”
- Right click on the query you created previously and click “Reference”
- Rename the reference table then “Close & Apply”
- From here you need to follow the same process as explained before
- All done!
Thanks you. Great post!
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.
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.
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?
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
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!
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.
A great solution to the issues you have described. One must take into account the impact on “drill trough” functionality in client tolls, that based on measure’s “home” table (unless otherwise specified).