Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Gender | Marrital Status | valid from | valid to | BorrowerKey |
Male | Single | 8/24/2022 | 9/5/2022 | a |
Female | Single | 9/6/2022 | 9/19/2022 | a |
Male | Single | 9/20/2022 | 12/31/9999 | a |
Male | Single | 8/24/2022 | 8/30/2022 | b |
Male | Married | 8/31/2022 | 12/31/9999 | b |
Female | Married | 8/31/2022 | 8/31/2022 | c |
Female | Single | 8/31/2022 | 12/31/9999 | c |
Female | Married | 8/31/2022 | 12/31/9999 | d |
Male | Single | 9/20/2022 | 12/31/9999 | e |
Bankruptcy Table:
Discharge | Bankruptcy Status | valid from | valid to | BorrowerKey |
Discharged | Restrictive | 8/24/2022 | 12/31/9999 | a |
Not Discharged | Restrictive | 8/31/2022 | 12/31/9999 | b |
Not Discharged | Restrictive | 8/31/2022 | 9/19/2022 | d |
Discharged | Restrictive | 9/20/2022 | 12/31/9999 | d |
Discharged | Unknown | 9/20/2022 | 12/25/2022 | e |
Discharged | Restrictive | 12/26/2022 | 12/31/9999 | e |
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,
but when I plot a table visual by dragging fields from both tables, I got this error:
On the other hand, if I directly connect 2 tables with many-to-many relationship, I can get the result I want:
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!
can anyone help please?
User | Count |
---|---|
77 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
99 | |
92 | |
50 | |
49 | |
46 |