Enhancements to Power BI Direct Query Mode

Prerequisite:

Power BI Desktop tool, SQL Server Database

Purpose of the setup:

Purpose of this blog is to demonstrate connecting SQL as a data source to Power BI Desktop Tool using Direct Query Mode.

Procedure:

To get this done we have created a Power BI Desktop Tool file with source data of SQL file connected using Direct Query mode.

As we know, previously there were many restriction in using Direct Query Mode like we could not view the relationship, cannot create calculated columns and measures etc. But now, there are many updates in Direct Query mode few of which will be demonstrated below.

1. Ability to create calculated columns in Direct Query.
2. Ability to create measures in Direct Query.
3. Ability to change the datatype when connected to Direct Query.
4. Ability to assume Referential Integrity on relationships in Direct Query

Ability to create calculated columns in Direct Query

Now we can create calculated column in Direct Query using DAX function.

Right click on the table area and select calculated column then write the required DAX query to get the column.

Below is an example where we have created a calculated column in Direct Query.

Ability to create measures in Direct Query

Now we can create measures in Direct Query using DAX function.

Right click on the table area and select measures then write the required DAX query to get the column.

Below is an example where we have created a measure in Direct Query.

Ability to change the datatype when connected to Direct Query

Now we can also change the datatype in Direct Query.

Ability to assume Referential Integrity on relationships in Direct Query

A new setting has been added to the Edit Relationship dialog: a checkbox to “Assume Referential Integrity”.

This setting is applied to imported and created data model relationships.

To access the setting select the Home tab -> Manage Relationships, select a relationship, and click Edit.

Here we have added few tables to our Direct Query file to create some relationship between the tables and selected one relationship to get the Referential Integrity option.

 
This is an advanced setting, and is only enabled when connecting to the data in Direct Query mode.

It will enable more efficient queries to be generated when it is known that:

1. The From column of the relationship is never Null/blank
2. For every value of the From column, there is a corresponding value in the To column

By setting the property in these cases, it allows more efficient queries to be sent to the backend database using INNER JOINs instead of OUTER JOINs. This will result in faster loading of dashboards for the end user.