Multiple fact tables into one dimension

[hasnain0204]

Hey guys, I have created one dimension through two different fact tables. The column I needed was Addresses and both the fact tables have them and I just wanted to have one new dimension table instead of two for the fact tables. I gave them unique values in the dimension table and merged the dimension table back with both the fact tables. I also have other dimension based on the two fact tables such as building name, region, etc. The issue is cross-filtering between these dimensions. I am thinking about making just one dimension with all the data inside but this is not the ideal solution. I've watched some videos on Youtube but i found them vague for my user case. How can I tackle this issue?

I am working with strings only at the moment. As the data is mostly real estate so buildings, regions, addresses, etc.

[johnt75]

You should be able to create a dimension table which will filter both fact tables with something like

Dim_Addresses =
DISTINCT (
    UNION ( DISTINCT ( 'Fact1'[Address] ), DISTINCT ( 'Fact2'[Address] ) )
)

Create one-to-many relationships from the dimension to both fact tables and everything should work.

[hasnain0204]

hey, I didnt explain the issue properly. Its not a relationship issue more like a multiple dimension issue. 

[johnt75]

Dimensions can filter multiple fact tables, that's one of the benefits of using them. If the filters aren't working as expected then the problem is likely in the relationships. Dimensions should filter fact tables, not the other way around, and there should be no links between fact tables.

[hasnain0204]

so, i am trying to filter dimension tables with other dimension tables and this is causing the problem. 

[johnt75]

Lets say you've got the situation 

DimA -> DimB -> FactA

so that you put a filter on DimA and want it to reach FactA. All the values you filter for in DimA must appear in DimB otherwise the filter won't reach FactA.

A better option would be to consolidate both dimensions into a single table.