Conditional table relationships

[Anonymous]

Hello,

I have 3 tables, Sales, Store and DistributionCenter. And these are my tables schemas em relationships:

Sales		Store		DistributionCenter
storeID	---->	storeID
distributionCenterID		storeName		distributionCenterName
specialCode		distributionCenterID	---->	distributionCenterID

 
And these are my tables data:
 

Sales
storeID	distributionCenterID	specialCode
1	1	1
1	1	1
1	1	2
1	1	2
2	2	1
2	2	2
2	2	2

Store
storeID	storeName	distributionCenterID
1	StoreFoo	1
2	StoreBar	2

DistributionCenter
distributionCenterID	distributionCenterName
1	Foo
2	Bar
3	Special

So, the Sales tables is connected with stores' tables and the table Stores is connected with the table DistributionCenter. The problem is if the value of specialCode column on Sales table is 2, then the right distribution center is Special and the storeID can not be changed.

For example, a sale with specialCode as 1 and storeID as 1, points to distribution center 1 (Foo). But if the specialCode is 2, then it must points to distribuition center 3 (Special). Is it possible to accomplish that without changing the relationships?

Thanks.

[parry2k]

You should add a calculated column in your sales table for distributioncenterid and then link this new column directly from sales table to distributioncenter table.

[Anonymous]

@parry2k, thanks for helping me. I known that I can do that. But I do not want to change my relationships. So is it possible to do it without changing it?

[parry2k · Accepted solution]

@Anonymous we can try this by adding new column in sales table:

Dist Name = 
var d= RELATED(Dist[distributionCenterName]) 
return if(Sales[specialCode]=2, LOOKUPVALUE(Dist[distributionCenterName], Dist[distributionCenterID], 3), d) 

[Anonymous]

@parry2k, thank you, your solution solved my problem!