Decoding DirectQuery in Power BI Part 3: Columns in DirectQuery

[amitchandak]

Problem description:

Add columns in Power BI using DirectQuery mode.

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.

Solution:

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

After forming the relationships, we created a measure for gross sales i.e. (Qty * Price).  For the required line-level calculations, we used SUMX.

Gross Sales M = sumx(Sales,[Qty]*[Price])
Discount M = sumx(Sales,[Qty]*[Price]*(Sales[Discount Percent]/100)) 
Net Sales M = [Gross Sales M] -[Discount M]

Using Data transformation, we opened Edit Queries mode to use the Custom Column feature. We created a Custom Column - Gross Sales CC with the same calculation i.e. (Quantity * Price). After saving it, we used the “Close and Apply” option to come out from Data Transformation mode.
Note that because Power BI allowed this custom calculation, we were able to add it to the table.
Using the same method, we also created Discount CC and Net Sales CC custom columns.

Gross Sales CC = [Qty]*[Price]
Discount CC = [Qty]*[Price]*[Discount Percent]/100
Net Sales CC = [Gross Sales CC] - [Discount CC]

In the same manner, we created a Calculated Column using DAX for Gross Sales and named it Gross Sales C. We also created Discount C and Net Sales C calculated columns using the Calculated Column feature.

Gross Sales C = [Qty]*[Price]
Discount C = [Qty]*[Price]*[Discount Percent]/100 
Net Sales C = [Gross Sales C] -[Discount C]

Measures, Custom Column, and Calculate Column have given us the desired results. We have validated these results against the database. All these calculations are correct. We will continue to test the Power BI DirectQuery for various calculations that are possible column-wise.

Let us know if you come across any issue or have any questions regarding Columns in DirectQuery.

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