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 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.

Interesting Links
Need Help
Tips and Tricks

5 Replies

  • 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.