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
Community Champion
5 years ago

Anonymous Well technically between geographies and members tables you have a many to many relationship. So if you are not using your bridge table in your visual then you could get weird results I am thinking. Can you mock up some sample data that exhibits this issue?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Greg_Deckler , sorry I'm not quite sure what you mean (fairly new to Power BI!). What's a bridge table?

 

Thanks!

H

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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 

       

      I see! I've looked into this a bit more now and had a play around. I seem to have solved the problem -- you're right it's caused by the many-to-many cardinality in what's a poorly structured data model. I've read up on model structures and rejigged my model into a star schema and it seems to have solved the problem (so far!)

       

      Thanks for your help!

      H

    • 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