How to calculate with data across multiple tables

[skdh_01]

Hi PBI Community,

I have a few tables queried from SQL DB (with those necessary columns extracted below) and I need to create various visuals calculating the order_quantity / charge_amount based on the different cust_desc for shipper, consignee and charge party respectively.

Originally I wanted to use DAX measures to filter and calculate the figures directly but I failed as I couldn't figure out how to filter across tables.

Currently what I am able to do is to use Merge Query a few times to put together all the below columns in order_table :

1) cust_type, cust_desc of shipper_code

2) cust_type, cust_desc of consignee_code

3) charge_party_code, and its cust_type, cust_desc

So that I can use CALCULATE together with FILTER for columns in the same table.

As it seems merge query can be a burden to performance (I suppose this amount of data is not small), would anyone help me on how the calculation can be done via DAX ? After all I think I should not carry on merging queries for future analyses on other columns / tables.

order_table = approx 80,000 rows

order_date		order_no		shipper_code		consignee_code
2024/5/25		order_001		cust_A		cust_L
2024/5/26		order_002		cust_A		cust_M
2024/5/27		order_003		cust_B		cust_N

order_sub_table = approx 150,000 rows

order_no		order_quantity		comm_type
order_001		5		normal
order_001		10		special
order_002		10		normal
order_003		20		normal

Relationship of "order_no" built with order_table (one to many)

order_charges_table = approx 150,000 rows

order_no		charge_amount		charge_party_code
order_001		100		cust_A
order_002		500		cust_M
order_003		700		cust_Z

Relationship of "order_no" built with order_table (one to one)

cust_table = approx 200,000 rows

cust_code		cust_type
cust_A		R
cust_B		V
cust_L		A
cust_M		F

Relationship of cust_code built with order_table (for shipper_code, consignee_code), also with order_charges_table (for charge_party_code), all are one to many, but only the relationship with shipper_code is currently active while the other 2 are inactive

cust_decode_table

cust_type		cust_desc
R		Regular
V		VIP
A		Agent
F		Referral

Relationship of cust_type built cust_table (many to one)

Appreciate any other comments / suggestions on the setup. Thank you.

[marcosvin]

Hello!

Analyzing your scenario, to avoid very heavy MERGE commands in PowerQuery (as your data source is the SQL database) an alternative could be to bring the data through SQL queries, where you could even build the fact and dimension tables (if you are not familiar with this dimensional modeling technique, take a look here), as this would leave the data organized to create metrics directly in the fact table (a FACT_ORDERS table could be created, for example).

If it is not possible for you to do this, you can also improve MERGE performance by creating dataflows from the Power BI service instead of using PowerQuery from Power BI Desktop (more details about Power BI dataflows here).

[skdh_01]

Thanks @marcosvin. Went through the dimensional modeling technique guide and learnt quite a number of key concepts there (esp snowflake and role-playing dimenions). But sorry being new to PBI I need your further advices.

For "an alternatice could be to bring the data through SQL queries", do you mean to add SQL statements to merge the columns of multiple tables when I first connect to a DB ? Not sure if I understand correctly, given my existing DB tables' structures are not well organized in terms of the PBI requirement, I should kind of manipulate the tables, putting facts / dimensions together before they are taken into PBI desktop for further handling.

If that's the case, I will go on to explore this as I have no knowledge in writing those statements now.

[marcosvin · Accepted solution]

Hello!

Exactly, in theory, the closer to the data source you make the transformations to organize the data, the better the performance can be (of course, it is important to test to validate).

For example, a SQL query to join the columns of the "order_table" table with the "order_sub_table" table would look like this:

SELECT ot.order_date,ot.order_no,ot.shipper_code,ot.consignee_code,ost.order_quantity,ost.comm_type
FROM order_table ot
LEFT JOIN order_sub_table ost
ON ot.order_no = ost.order_no

In Power BI Desktop, when importing data from a database, in "Advanced options" a "SQL Statement" text box appears for you to paste your SQL query, like the query above. This step would be important to avoid carrying out many merge operations within Power BI Desktop, and the organization into fact and dimension tables would be important to facilitate the creation of metrics and relationships between tables.