How to Relate two Tables When there are Multiple Paths

[delish]

I have two tables:
1) Sales Invoice: denormalized sales header and line item so that the lowest level of granularity is the line item.
2) Freight Charge: freight charges that could be at either header or line item level.

Current model:
Sales Invoice table has a Freight Charge measure that is allocated down to the table's granularity (line-level). If it is line item freight than it's left as-is. If it's header freight then I allocate the freight evenly across the line items. This is all done in the table's source SQL.
Freight Charge table is brought in as-is from the source system and has rows related to the Sales invoice at both the header and line-levels (one or the other). I have the Sales Invoice header Id in all rows. I have the Sales Invoice line Id in all rows where the freight charge is at the line item-level.

Problem:
These two tables work as expected when they are used independently. The problem is that the users want to be able to drill through from the Sales Invoice table to the Freight Charge table and see the related charges. It seems easy enough to relate the two tables using the Sales Header Id. However, if the user is viewing a report that is down at the line-level and drills through to Freight Charges, they would get all charges related to the Invoice (all header and line-level charges) when they should only see the related line-level charges.

I have a feeling I'm overlooking a simple modeling issue. What I don't want to do is use DAX in every report to control this behavior. I'd like it handled once in the model. Any ideas?

[v-juanli-msft]

Hi @delish 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[Anonymous]

Hi @delish  i think that you problem is a n to n relation in the model. In order to correct this situation, you need like i call it a "bridge table", wich have in this the fact distinct table´s primary key, and whit this u can create a "n to 1" and "1 to n" relation. 

Also u need have the relation in both directions, this can setup in each relation, with double click over the relation line and change the option to both in Lower right corner

For this solution, u need to duplicate the fact table, delete all the columns except the primary key, and then delete duplicates in order to have distinct values for primary jkey. 

I leave you a .pbix with the examble, in this case my fact is hoja3 and the second table is hoja4, and the key for relation is fecha_uno.  

https://1drv.ms/u/s!AtEkAF7ffIsqg8RVODnlZbEd4YtOuw?e=ZigpeN

Let me know if my solution is helpfull. 

Thanks and regards.

[delish]

Thank you for the suggestion. Unfortunately this is not a many to many issue. The issue is that I want to be able to see the rows related to either the header or the line depending on the context.