31 Mar’16

BIEnhancements 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.

(a)

 

(b)
Fig 1: Power BI Desktop Tool file 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.

Fig 2: Create New Column option in Direct Query Mode

 

Fig 3: Created calculated column in Direct Query Mode

 

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.

Fig 4: Create new Measure option in Direct Query Mode

 

Fig 5: Created Measured in Direct Query Mode

 

Ability to change the datatype when connected to Direct Query

Now we can also change the datatype in Direct Query.

Fig 6: Change Datatype option in Direct Query Mode

 

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.

Fig 7: Assume Referential Integrity in Direct Query Mode

 
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.

Written by

Team Member

CloudFronts

Leave a Reply

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

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more