Forum Discussion
Bridging Table - Relationships
Hi guys.
This is more a database-related question, but it is PBI related anyway.
I have two FACT (FACT 1, FACT 2) tables and two DIM (DIM 1, DIM 2) tables. Both have different sales data. I need to connect them to do some benchmarking of costumers. The data structure is below.
Where it gets tricky is that for each ID 1 there may be several ID 2. For example, two distinct costumers on DIM 2 may share the same ID1 (because it's the same VAT number and owner, for example).
I know that for these situations you are supposed to do a bridging table and I understand the concept. What I can't figure out is the relationships. I think I need them to work both ways. While from ID1 to the Bridging Table there could be a 1-to-1 relationship, it could work both ways, but from the Bridging Table to DIM 2 it would be 1-to-many, so it couldn't work both ways.
Am I missing something here? I am assuming I should connect the Bridging Table with the DIM tables, is that a correct assumption for a start? Once again I know this is more of a Database theory question but I feel like other people might face the same challenge using PBI.
Thanks in advance.
Cheers!
Just to close this thread, I used a 1 to many relationship with both ways, it works.
I just had to be careful for the cases where there were 1 ID1 for many ID2, so that I could get something like an average value I used a measure like this:
COUNT IDS = COUNTROWS ( FILTER ( ALL ( 'Bridging Table' ); 'Bridging Table'[ID1] = MIN ( 'Bridging Table'[ID1] ) ) )
5 Replies
Hi Anonymous,
In the relationship with bridge table, one of them will be a single directional relation while the other should be a bi-directional relation. In that case, it will work both ways
Find below the screen capture of the same
Below showing the data in the tables
Regards,
Thejeswar
Thanks for your reply.
Will it work from right to left (diagram you shared)?
Suppose I have two slicers on the visual, ID1 and ID2. The way I see it now, if I filter by ID1 it will affect the table FACT 2, but if I filter by ID2 it will not affect FACT 1.
Does this make sense?
Cheers.
