Learn how to Outline a Measure Desk in Energy BI Desktop


On this submit I present you a easy trick to make your Energy BI mannequin extra organised and extra readable. I name it making a “Measure Desk”. Let me clarify. The story is that I used to be engaged on a mannequin with a number of tables. The database schema was NOT a correct star schema so there have been a bunch of measures unfold into a number of totally different tables. On prime of that we’ve created a number of calculated measures with totally different house tables which made it actually laborious to discover a explicit measure or calculated measure. I believed, properly, when it’s that arduous to seek out the calculated measures at improvement time how laborious it might be for a buyer to seek out, perceive and use the measures we created. The visibility of the calculated measures might be a problem when we’ve got a number of measures in a number of totally different tables. You’ll quickly really feel the difficulty in buyer coaching classes when you could navigate between a number of totally different tables to discover a calculated measure.

Think about you create a Energy BI mannequin with direct hook up with a SSAS Multidimensional occasion. You’ll instantly discover that each one measure teams have a particular calculation icon (Measure Group Icon in Power BI) quite than a traditional desk icon (Table icon in Power BI) which makes the measure teams extra recognisable for the top customers. For example, you may simply discover any calculated measure associated to “Web Gross sales” below the “Web Gross sales” measure group.

Measure Groups in SSAS Multidimensional Dirct Connect

I do know, we will search and discover the measures very simply, however, our mannequin can be extra organised and extra consumer pleasant if we will put all measures in a number of tables which include simply associated calculated measures and nothing else. For example, we will create a measure desk for time intelligence calculations and identify it “Gross sales Time Intelligence Measures” and put all  calculated measures like “Gross sales YTD”, “Gross sales LYTD”, “Gross sales Interval Over Interval” on it. It is going to make your mannequin good and clear, simple to make use of and straightforward to study in your clients. It is going to additionally assist you to coach your clients extra simply.

On this article I’ll hook up with a SQL Server occasion and can use the well-known Journey Works database. I additionally present you the right way to get the job accomplished in each “Import” and “DirectQuery” modes as there are some limitations utilized to the DirectQuery mode which makes it more durable to do what we would like.

Lets begin.

The thought is including a brand new desk with only one column having only one worth in that column. Then we have to cover that column. This makes the entire desk get hidden as there isn’t any columns to point out. Then we use this desk as “Dwelling Desk” for all associated calculated measures. Keep in mind, we do NOT cover the desk, however, simply the column. On this case Energy BI Desktop recognises the desk as a measure desk so it makes use of the measure group icon (Power BI Measure Group Icon) for it.

Create a Measure Desk in Import Mode

  • Open Energy BI Desktop
  • Get information
  • Connect with a SQL Server Database (Journey Works as a pattern)

Power BI get data from SQL Server 01

  • Choose a desired desk (FactInternetSales in our case)
  • Click on “Choose Associated Tables” button
  • Click on “Load”

Power BI get data from SQL Server 02

  • Click on “Import” then OK

Power BI Get data from SQL Server Import mode

  • Click on “Enter Information” from Dwelling tab

Power BI Enter Data

  • Enter a worth for “Column1”
  • Enter a reputation for the desk then click on “Load”

Power BI Create Table

  • Broaden the brand new desk from the “Fields” pane then cover the “Column1” column

Power BI hide columns

  • This can cover the entire desk as there isn’t any seen columns to point out
  • Click on “New Measure” from “Modeling” tab from the ribbon to create a brand new calculated measure

Power BI new measure

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

Power BI new measure DAX

  • This can create the calculated measure within the first desk within the “Fields” pane

Power BI new measure 02

  • Click on on components bar once more to allow the measure properties
  • Click on “Dwelling Desk” and decide the newly created desk from the listing

Power BI new measure change home table

  • As you may see the desk seems on the “Fields” pane once more with the calculated measure

Power BI Task pane

  • As you may see the tables nonetheless has the traditional desk icon, however, should you toggle proper the fields pane then toggle left it the icon shall be refreshed

Power BI Toggle Task pane

You possibly can create extra tables like “Resellers Gross sales” and so forth then transfer the calculated measures to the corresponding tables. In some circumstances you may cover the entire truth tables when you have created all calculated measures you want or when your buyer gained’t must see any measures from the very fact tables.

Power BI Table of Measures

Create a Measure Desk in DirectQuery Mode

Mainly we have to do the identical course of, however, this time we wish to hook up with a SL Server database in DirectQuery mode. So to get information we do precisely the identical issues:

Open Energy BI Desktop=> Get Information=> Connect with a SQL Server database and so forth.

It will be significant that you simply enter the database identify at step one of getting information.

Power BI get data from SQL Server 02

However, you’ll instantly discover that the “Enter Information” button is disabled in DirectQuery mode.

Power BI Enter Data disabled in DirectQuery

It’s OK. There’s at all times an alternate solution to obtain the purpose.

As I discussed earlier than, we have to enter the database identify when getting information. That is essential as we’ll reuse the connection within the subsequent steps and if we haven’t enter the database identify you’ll get the next message:

“Connecting to tables from a couple of database is just not supported in DirectQuery mode”

Power BI connectin to different datanases is disables in DirectQuery

OK, right here is the trick.

After you linked to the SQL Server database in DirectQuery mode observe the steps beneath:

  • Click on “Edit Queries” from the ribbon

Power BI Edit Query

  • Click on “Current Sources” then click on the newest one

Power BI reuse existing source

  • Choose a desk. It doesn’t matter which desk you choose as we’ll modify it throughout the subsequent steps. Click on OK

Power BI Navigator

  • This can add the brand new desk within the “Queries” pane (in my pattern it’s “AdventureWorksDWBuildVersion”)

Power BI Query Editor

  • Click on “Superior Editor” from “Dwelling” tab from the ribbon

Power BI Advanced Editor

Power BI Advanced Editor 02

  • All you could do is so as to add a easy question like “[Query=”SELECT 1 AS NEW_COLUMN”]” to the supply

Power BI Advanced Editor 03

  • Now you need to remark out/take away the subsequent line in addition to the output within the “in
  • Put “Supply” within the “in” block
  • Don’t forget to take away the comma “,” from the top of the “Supply” line
  • Click on “Achieved”

Power BI Advanced Editor 04

  • It is best to see a desk with one column

Power BI new table in Query Editor

  • Rename the question then click on “Shut & Apply”

Power BI new table in Query Editor 02

OK, now we’ve got a desk with only one column. From right here the remainder of the method is rather like what we’ve accomplished earlier than.

Power BI new table in Fields pane

  • Conceal the “New_Column” column
  • Add a brand new measure then change the “Dwelling Desk” to newly created desk
  • Toggle proper and left the duty pane and also you’re accomplished

Power BI Table of Measures 02

You would possibly suppose

“Effectively… this was a bit lengthy process. What if I wish to create a couple of measure desk? Do I must redo the entire course of time and again?”

The reply is NO, you do NOT should redo the method for every measure desk you wish to add to the mannequin. All you could do is to create a reference question from the question you already created.

To take action:

  • Click on “Edit Question” from the ribbon to open “Question Editor”

Power BI Edit Queries 02

  • Proper click on on the question you created beforehand and click on “Reference”

Power BI reference table

  • Rename the reference desk then “Shut & Apply”

Power BI reference table 02

  • From right here you could observe the identical course of as defined earlier than

Power BI Table of Measures 03



Supply hyperlink

Leave a Reply

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