Bridging Table - Relationships

[Anonymous]

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!

[Thejeswar]

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

[Anonymous]

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

[Thejeswar]

Hi @Anonymous,

Yes I think.

I tried creating a filter on both the IDs (ID1 and ID2) separately. I can see the report getting filtered corresponding to the slicers selected

Hope this helps!!!

Click here for the PBIX file

[Anonymous]

For me it works like I was expecting, only from left to right.

I guess I'll have to live with that.

I'll just wait a day or so to see if someone finds an alternative, otherwise I will mark your reply as solution!

Thanks a lot!

[Anonymous · Accepted solution]

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] )
    )
)