Power BI composite Models

For this example, add a Table in Direct Query mode from SQL Server. Here I’m using my AdventureWorks DB from my local database

Add another file (any Excel file you have) Direct Query is not available for Excel. Before Composite models was available, you couldn’t add Excel if your other table was direct query. Everything had to be the same import mode

Close and Apply

Now Composite model is available you can mix storage models. However you need to be aware that the Direct Query will have actual data within the query (SQL) rather than all the querying being done within Power BI

You can hover over the tables to see what table storage is set for each table

Note that we can see the data for Excel which can only be Imported data

However we cant see the data in the data pane for the Direct Query Fact table

We can go to the model view and connect the tables together

To show this I’m going to add Dim date from the SQL Data base as an Imported table (Remember the fact is a direct query)

A composite model is one where tables have different Storage types. there are now three storage types.

  • import – the Best way to use Power BI because everything is cached in memory
  • Direct Query – As above Power BI will query your data source and your measures and calculated columns will be created within SQL if you are connecting to a SQL database
  • Dual – can be both Import or Direct query. We will look at this in more detail later

live Query is only available using analysis services as a data source. In this case you can only use SSAS and no other data source.

Power BI grouping fields within a table

I have a fact table that now contains many measures and calculated columns. So many in fact that its quite difficult to scroll through.

There is now a way to group all your fields within a table to make it easier to use.

Lets look at the first group. I have lots of measures relating to rolling Quarter. These can all go in a folder together

Go to model view where you can find Properties for the fields

Click on one of the fields in your table that you want to group and set the Display folder name

You can then go through each field and set the display folder to group the measures

To speed up the process, hold down ctrl and select multiple columns and set the Display column for all the selected fields.

this makes it much easier to look after your measures

Create your website with WordPress.com
Get started