Need help understanding why 2 fact tables cannot refer to the same lookup tables

[Roseventura]

I have two fact tables.  They are joined with several lookup tables (see relationship screen shot below).

Fact table A stores RMA’s as they relate to the supplier. 

Fact table B stores RMA’s as they relate to the Customer.

Lookup tables are these:

                Date table

                Vendor Lookup

                Item Master (to link the part numbers)

                RMA Master (which stores ALL the RMA numbers)

                RMA Disposition  (current status of the RMA)

                RMA Reason (current reason for the RMA)

Each one of my lookup tables contains info that both fact tables need to refer to.  I’m having a hard time understanding why, if you have 2 independent fact tables and several lookup tables, each fact table cannot refer to each of the necessary lookup tables.  Why is it that I can have a direct link between Fact table B to the RMA Master, but not with Fact table A?

I could see if Table A and B were linked in some way, but they are both independent of each other and I need to have Table A use the lookup (RMA Master) the same way Table B does.

Any assistance would be great appreciated.

 

[Anonymous · Accepted solution]

This might get quite confusing due to circular paths but please bear with me.

While Fact table A and Fact table B aren't directly linked, if you look at your current tables, you'll notice this:

• Fact table A is related to the table to the left of Table RMA
• Fact table B is also related to the table to the left of Table RMA

This means that, as far as Power BI is aware, Table A is linked to Table B, but indirectly. Since Table B is already linked to Table RMA, when you try to link Table A to Table RMA, Power BI's thinks it can use the indirect relationship between Table A and Table B to just guess what RMA number to use for Table A. When you try to add a link between Table A and Table RMA, Power BI doesn't have the means to decide what path to take anymore (Table A -> Table RMA vs Table A -> other table -> Table B -> Table RMA)

Hopefully that explains why both relationships can't be active at the same time.

That said, solving the issue is a whole other can of worms. I don't have quite enough experience to know what the best practice is, especially without knowing what graphics/operations you're planning, but taking a look at the LOOKUPVALUE function might cover some of the functionality you need?