Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
yuanye0710
Frequent Visitor

Issue when using a bridge table to model many-to-many relationship

Hi friends, I'm trying to create a model for 2 tables: Borrower and Bankruptcy. Sample data looks like below (uploaded a sample to github: https://github.com/yuanye710/PBI)

 

Borrower Table:

GenderMarrital Statusvalid fromvalid toBorrowerKey
MaleSingle8/24/20229/5/2022a
FemaleSingle9/6/20229/19/2022a
MaleSingle9/20/202212/31/9999a
MaleSingle8/24/20228/30/2022b
MaleMarried8/31/202212/31/9999b
FemaleMarried8/31/20228/31/2022c
FemaleSingle8/31/202212/31/9999c
FemaleMarried8/31/202212/31/9999d
MaleSingle9/20/202212/31/9999e

 

Bankruptcy Table:

DischargeBankruptcy Statusvalid fromvalid toBorrowerKey
DischargedRestrictive8/24/202212/31/9999a
Not DischargedRestrictive8/31/202212/31/9999b
Not DischargedRestrictive8/31/20229/19/2022d
DischargedRestrictive9/20/202212/31/9999d
DischargedUnknown9/20/202212/25/2022e
DischargedRestrictive12/26/202212/31/9999e

 

For my case, clients could update their info and server will log the new info by inserting a new row. Every row has a [valid from] and a [valid to] column indicates when the info of that row was valid.

 

As a result, this is a many-to-many relationship because every borrowerkey could exist multiple times in each table. I created a bridge table to host a list of [borrowerkey] and build up the relationship,

yuanye0710_6-1678717395093.png

yuanye0710_4-1678716720967.png

 

but when I plot a table visual by dragging fields from both tables, I got this error:

yuanye0710_0-1678717603490.png

 

yuanye0710_0-1678716466994.png

On the other hand, if I directly connect 2 tables with many-to-many relationship, I can get the result I want:

yuanye0710_5-1678716759268.png

yuanye0710_1-1678716578632.png

As a best practice I'm trying to avoid direct many-to-many, but I'm not sure why using a bridge table doesn't work for me. Could anyone me understand the issue and try to fix it? Thanks!

1 REPLY 1
yuanye0710
Frequent Visitor

can anyone help please?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.