connect 2 data tables with relationship between them with 1 third lookup dim for both of them

[Anonymous]

Hello...

I have 2 data tables to monitor, one of them is clean work sector field, and one is unclean

both fields are connected to the same dim... in past, I was pulling 2 dim tables one for clean and one for unclean data table in SSAS tabular...

but it was a little confusing, they were asking, why you have 2 dim for the same business meaning since both dim have the same elements.. so I'm trying to find a solution to connect the 2 data tables with 1 only dim but at the same time I don't want to break 

the relationship between 2 data tables since I have over 150 measures and I want to use in both without double them in both data tables..

when I try the solution above it gave me an inactive relationship... how can I solve it?

[v-shex-msft]

Hi @Anonymous,

Perhaps you can try to create a bridge table with unique values extracts from two tables and use the dimension table to link with this bridge.

In addition, you can also try to merge two table records and add a custom field to remark where these records from. Then you can add this additional field as a filter to fix your measure expression.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@Anonymous Well, you didn't include your relationship directions but I can guess the directions. You have an inactive relationship because you have 2 pathways from your dim table to both of your fact tables. Couple of questions, why the 1:1 relationship between your fact tables? Could you collapse your 2 fact tables to 1? Could you create a different dimension based upon what is forming your 1:1 relationship between your fact tables and thus remove the the 1:1 relationship and utilize this 2nd dimension instead?