Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

amitchandak
Super User
‎08-05-2020 11:19 AM

Decoding DirectQuery in Power BI Part 4: Complex Columns in DirectQuery

Problem description:

In the previous blog, we discussed how to use calculated columns and customer columns in the direct query. In this blog, we will further explore the column calculation to use IF and DATEDIFF.

 

Setup:

I have installed SQL Server Express edition on my machine and uploaded four tables -customer, geography, item, and sales.

 

Screenshot 2020-08-02 18.48.12.png

 

Data Loading:

Connected to SQL server in DirectQuery mode and edited all these 4 tables.

 

Screenshot 2020-08-02 18.51.47.png

Steps:

We created the required joins between Sales and customer, sales and geography, sales and item. All joins have one to many relationships from the dimension table to the fact table.

Screenshot 2020-08-02 18.36.17.png

 

Next, we created a calculated column using IF statement (given below) and changed its AGGREGATION. Then use it into the visual without any challenges.

 

 

If C = if([Qty]>1,1,0)

 

 

Screenshot 2020-08-02 21.28.13.png

 

 

Using Custom Columns in the power query, we were able to create the column. The data type was set to ANY. And we were not able to aggregate this column.

 

Screenshot 2020-08-02 21.29.09.png

Note: We weren’t able to change its data type in edit mode.

Message: The change of data type is supported only in import mode in the edit query.

 

However, we were able to change its data type using the Column tool. Using this strategy, we changed the data type to a whole number and were able to aggregate it to get the required results.

 

 

 

If CC = if [Qty] >1 then 1 else 0

 

 

 

Screenshot 2020-08-02 21.28.22.png

 

In the case of the calculated column, we tried DATEDIFF and by using DAX we were able to get the result without any problem.

 

 

Date Diff C = DATEDIFF([Sales Date],[Delivery Date], DAY)

 

 

 

Screenshot 2020-08-02 21.34.12.png

 

In the case of M/Edit Query, we were able to take the difference between two dates. Because we were not able to use DURATION DAYS. Duration.Days is not supported for DirectQuery Mode. The data type of duration is ANY.

 

Screenshot 2020-08-02 21.36.37.png

Conclusion:

 

In the direct query, we can use IF function to create some complex logic in both Calculated Column(DAX) and Custom Column(M). Although there are few functions that aren’t allowed in the indirect Query Mode in both Calculated and Custom Columns, we can still take advantage of allowed calculations in the columns present in direct query mode.

 

Do share your experience with DirectQuery and let us know if you want to check out something different in DirectQuery mode.

 

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403