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

[amitchandak]

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.

Data Loading:

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

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.

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)

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.

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

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) 

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.

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