Forum Discussion

brirack's avatar
brirack
Regular Visitor
3 years ago

Relationships between multiple tables and a date table

Hi,

I'm sure this is super simple but I can't figure this out...

I have a table (sign-ups) with a count of employee numbers by L1 that have signed up to a product. I have another dataset (headcount) of total headcount - all of those who COULD have signed up. I want a table with Signups as a % of Headcount by each L1. I also have a date table built that filters both the signups & headcount and have a relationship between the date table and signups and headcount sets. When I build my table I add the L1 and the count of sign-ups and filter by month, I get the filter value for signups for e.g. June, but I get the Total headcount for that month - i.e not the L1 amount. This is because there is no relationship between sign-ups and headcount. But I can't create one as they are both linked to the date table already - help!RelationshipsTable output

Need Help

3 Replies

  • Bipin-Lala's avatar
    Bipin-Lala
    Solution Sage
    4 months ago

    Hi brirack,

    Filters only flow downhill from Dim tables to Fact tables. Your L1 filter is sitting inside one Fact table, so it can't reach the other.

     

    ​To make this work, you need a common bridge that both tables share, just like they share the Date table.

    • ​Create a unique list of L1s: In Power Query or using a DAX calculated table, create a new table that contains every unique L1 value from both datasets.
    • ​Build the Relationships: Connect Org Hierarchy [L1] to Signups [L1] (1:Many).
    • Connect Org Hierarchy [L1] to Headcount [L1] (1:Many).
    • ​Update your Visual: Remove the "L1" column you currently have in your table visual and replace it with the "L1" from your new Org Hierarchy table.

     

    There is another easier way to fix this which is changing the relationship between the date and fact tables to bi-directional relationship. It's not the recommended way because it can lead to circular dependencies, but no harm in giving it a try.

     

    Let me know how it goes!

  • mickey64's avatar
    mickey64
    Super User
    4 months ago

    For your reference.

     

    Step 1: I make a 'L1_Table' below.

        L1_Table = SUMMARIZE(‘WS_Raw Data,’WS_Raw Data’[L1])

     

    Step 2: I add two relationships and change two type of relationships, 'Both --> Single' below.