Need help on DAX measure - fetching not related data

[mahenkj2]

Hi,

I need help on creating 3 measures, namely TotaQty, TotalAmount, Amount/Qty.

There are 4 tables, all are connected with one to many in one by one cascade. I attach a sample .pbix file, it is a sample version:

https://drive.google.com/file/d/1LKp2GURuu3KDaPvfCc9Cuxo4n8iPY5ct/view?usp=sharing

in my original data, the measure TotalAmount never loads. I think its becuase it fetches all the rows from Table-3 which are not related to filtered FreightID.

As I add the TotalAmount measure to the visual, it fetches rows showing InvoiceID which are not related with original FreightID or LNo. I think I need to adjust these measure to work well.

My intended output is as below:

FreightID	LNo	InvoiceID	Qty	TotalQty	TotalAmount	Amount/Qty
1	1	1	200	2200	9000	4.091
1	1	1	400	2200	9000	4.091
1	1	2	200	2200	9000	4.091
1	1	2	600	2200	9000	4.091
1	2	3	300	2200	9000	4.091
1	2	4	500	2200	9000	4.091

Basically measures should ignore LNo and InvoiceID fields and fetch totals only based on FreightID.

Hope I provided adequate information to resolve it.

[v-tianyich-msft]

Hi @mahenkj2 ,

I've been researching this for a long time and couldn't come up with your expected results, but close. Your initial problem was that the relationship was not set up both ways. The problem now is that once you traverse the table, you get all the row data.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[mahenkj2]

Hi @v-tianyich-msft ,

Thank you for your time.

I saw the attachement but result is not what I am looking for, I see below result:

whereas in TotalAmount I wanted to see 9000 and in TotalQty =2200, I believe basically except FreightID, all columns in conext to go off.

Can I achieve that?
P.S. I depend on DAX very little, because just by optimum model design and Power Query, basic DAX is fine, but this time it looks like I need to dig into it or may be I am overcomplicating it.