Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Total in table sums incorrectly

Hi all,   I have a table that shows the number of members for a loyalty card scheme sliced by location:   The weird thing is that the total is correct -- I know that there's 48364 members i...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous Bridge table:

 

Table 1 *<---1 Bridge Table 1 --> * Table 2

 

Basically a bridge table is a distinct (unique) list of values in the columns that make up the relationship between tables 1 and 2. You can create it in Power Query or in DAX. In DAX it is:

 

Bridge Table = 
  DISTINCT(
    UNION(
      SELECTCOLUMNS('Table 1',"Column",[Column]),
      SELECTCOLUMNS('Table 2',"Column",[Column])
    )
  )
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Greg_Deckler 

 

In restructuring my data model I've encountered a new related problem that perhaps you know how to solve?

 

I have two fact tables -- one with membership data and another with benefits data. I want to link both of these fact tables to a dimension table of locations.

 

No problem there with the membership table -- both the members table and location table contain the postcodes to link them.

The problem is with the benefits table. The only key in the benefits data is the member ID. There's no location field in there to link it to the location. The way I previously linked locations with benefits was to create a relationship like this:

But I've read that you should never link two fact tables, so I don't want to do this.

 

I've also read that bridge tables can solve this problem, but I'm not sure how I'd do that here. I tried drawing out member ID into it's own bridge table, but because the members are dynamically updating, I get an error message saying that this creates circularity.

 

Any tips for how to handle this?

 

Thanks again,

H