Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Multiple fact tables into one dimension

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 instea...
Interesting Links
Need Help
Tips and Tricks
johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

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

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        1 year ago

        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.