Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 n...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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] )
    )
)
Thejeswar's avatar
Thejeswar
Super User
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Thejeswar
    Super User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

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