How do I connect both tables to the another two tables?

[Anonymous]

Hello,

I am having two tables: Shop1 and Shop2, they both have columns: ProductID and DescriptionID. There are two other tables: ProductTable (Columns: ProductID, ProductTitle) and DescriptionTable (DescriptionID, DescriptionText). I want to connect Product ID of Shop1 to ProductID of ProductTable and DescriptionID of Shop1 to DescriptionID of DescriptionTable. The same I want to do for table Shop2. I am able to do it for one table (either only Shop1 or Shop2), but not for both, because I am getting inactive relationship problem.

Can someone help me?

Thanks! 

[Burningsuit]

Hi @Anonymous 

In this situation you have a couple of options.

The first is to use the DAX command USERELATIONSHIP. 

USERELATIONSHIP is a CALCULATE modifier, which instructs CALCULATE to temporarily activate the relationship. When CALCULATE has computed its result, the default relationship becomes active again.

So you would create a Measure that uses the Inactive relationship you're already created, something like this.

MyMeasure = CALCULATE(SUM(Shop1[SalesAmount]), USERELATIONSHIP(ProductTable[ProductID], Shop1[ProductID))

see USERELATIONSHIP function (DAX) - DAX | Microsoft Docs

However it may be better for you to use Power Query to merge these two tables, Shop1 and Shop2 into one, so that you have a table with headings like this.

ShopNo  ProductID  DescriptionID

the column ShopNo will be either 1 or 2. That will make it easier to filter on ShopNo to show just figures for 1 or 2, with no inactive relationships required.

See  Solved: Joining contents of two tables into one new table - Microsoft Power BI Community

Hope this helps

Stuart