On this publish 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 plenty of tables. The database schema was NOT a correct star schema so there have been a bunch of measures unfold into plenty of totally different tables. On high of that we’ve created plenty of calculated measures with totally different residence tables which made it actually onerous to discover a specific measure or calculated measure. I believed, properly, when it’s that onerous to seek out the calculated measures at growth time how onerous 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 difficulty when we’ve plenty of measures in plenty of totally different tables. You’ll quickly really feel the difficulty in buyer coaching periods when it’s worthwhile to navigate between plenty of totally different tables to discover a calculated measure.

Take into account 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 (

) slightly than a traditional desk icon () which makes the measure teams extra recognisable for the top customers. As an example, you may simply discover any calculated measure associated to “Web Gross sales” underneath the “Web Gross sales” measure group.

I do know, we are able to search and discover the measures very simply, however, our mannequin could be extra organised and extra consumer pleasant if we are able to put all measures in a number of tables which comprise simply associated calculated measures and nothing else. As an example, we are able to 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 can make your mannequin good and clear, straightforward to make use of and straightforward to be taught to your prospects. It can additionally provide help to to coach your prospects 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 best 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 wish.

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 disguise that column. This makes the entire desk get hidden as there isn’t a columns to indicate. Then we use this desk as “Residence Desk” for all associated calculated measures. Bear in mind, we do NOT disguise 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 () for it.

Create a Measure Desk in Import Mode

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

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

• Click on “Import” then OK

• Click on “Enter Knowledge” from Residence tab

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

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

• This may disguise the entire desk as there isn’t a seen columns to indicate
• Click on “New Measure” from “Modeling” tab from the ribbon to create a brand new calculated measure

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

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

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

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

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

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

Create a Measure Desk in DirectQuery Mode

Mainly we have to do the identical course of, however, this time we need 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 Knowledge=> Hook up with a SQL Server database and so forth.

It is necessary that you simply enter the database identify at step one of getting information.

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

It’s OK. There’s at all times another method to obtain the objective.

As I discussed earlier than, we have to enter the database identify when getting information. That is vital 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 multiple database shouldn’t be supported in DirectQuery mode”

OK, right here is the trick.

After you related to the SQL Server database in DirectQuery mode comply with the steps beneath:

• Click on “Edit Queries” from the ribbon

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

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

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

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

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

• Now it’s a must to remark out/take away the following 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 “Finished”

• It’s best to see a desk with one column

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

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

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

You would possibly suppose

“Properly… this was a bit lengthy process. What if I need to create multiple measure desk? Do I must redo the entire course of many times?”

The reply is NO, you do NOT must redo the method for every measure desk you need to add to the mannequin. All it’s worthwhile to 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”

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

• Rename the reference desk then “Shut & Apply”

• From right here it’s worthwhile to comply with the identical course of as defined earlier than