Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, everyone,
I have been stuck a question for a long time.
For auto insurance policy, one policy comes some coverage, such as BI, PD and PIP.
Given one policy, there can be more than one accident. For each accident, more than one coverage might be involved.
For example, in the attached four tables, Policy table and PolicyData table are 1-M relationship. Policy table and Claim table are also 1-M relationship. Claim table and ClaimData are also 1-M relationship.
Question 1: how to build relationship among the tables
Policy and PolicyData is straight, 1-M.
But for Claim table and ClaimData table, can be Policy to Claim to ClaimData as one 1-M chain, also can be Policy-Claim (1-M), and Policy-ClaimData(1-M).
Which approach is optimal?
Question 2: how to combine all the information together, in a new Calculated table or Report?
The New Table (or Report?) should include: Policy Number, Effective Date, Coverage, Limit, Claim Number, Loss Date, Amount.
I preferred a Report solution.
I would like to thank you first.
Dennis
Policy | PolicyData | |||||
Policy Number | Effective Date | Policy Number | Coverage | Limit | ||
456 | 1/2/2014 | 456 | BI | 20 | ||
321 | 3/5/2014 | 456 | PD | 25 | ||
456 | PIP | 60 | ||||
321 | BI | 30 | ||||
321 | PD | 65 | ||||
Claim | ClaimData | |||||
Policy Number | Claim Number | Loss Date | Claim Number | Coverage | Amount | |
456 | 101 | 2/2/2014 | 101 | BI | 55 | |
321 | 205 | 4/8/2014 | 101 | PIP | 63 | |
456 | 506 | 4/6/2014 | 205 | PD | 44 | |
205 | BI | 78 | ||||
506 | PD | 55 |
Hi,
I think the relationships should flow like:
Hope this helps.
Thank you, Ashish,
Can I send this simple Power BI data to you for a test?
I can be reached at dli@cwico.com.
Sincerely,
Dennis
Hi,
You are welcome. Have you tried my solution? Has it worked?
User | Count |
---|---|
87 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |