cancel
Showing results for 
Search instead for 
Did you mean: 

Decoding DirectQuery in Power BI Part 3: Columns in DirectQuery

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.

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

 

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.

Screenshot 2020-08-02 18.36.17.png

 

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]

 

 

 

Screenshot 2020-08-02 18.35.53.png

 

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.

Screenshot 2020-08-02 18.39.05.png

 

 

 

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

 

 

 

Screenshot 2020-08-02 18.41.25.png

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]

 

 

 

Screenshot 2020-08-02 18.45.34.png

 

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.