Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
Please help me with the solution as I am a beginner in Power BI.
I am getting data from an online SharePoint List. Where Incident is Main List and Person and Witness are sub-lists.
So every incident can have N number of Persons (or/and )N number of Witnesses. I tried creating a relationship as below
Incident ID (Incident ) --1 to many --> Incident ID (Person )
Incident ID (Incident ) --1 to many --> Incident ID (Witness)
But when creating a table while adding all the columns from 3 tables I get the below error.
1. Incident Table ( The main table where Incident ID is always unique )
Incident_ID | Incident_Name | Status |
1 | Fire | In Progress |
2 | Blowdown Valve | Completed |
3 | Alarm | Canceled |
2. Person Table
ID | Incident_ID | Person_Name | Person_Staff_ID |
1 | 1 | Jack | 222 |
2 | 1 | Mack | 223 |
3 | 1 | Amy | 224 |
4 | 3 | Jack | 222 |
3. Witness Table
Incident_ID | Witness_Name | Witness_Staff _ID |
2 | Joan | 11 |
2 | Joseph | 12 |
1 | Alex | 13 |
I tried creating a relationship as shown below
But what happens in the table is the Witness Details get Repeated 3 times since the Person Details has 3 records.
Incident_ID | Incident_Name | Status | Person_Name | Person_Staff_ID | Witness_Name | Witness_Staff_ID |
1 | Fire | In Progress | Jack | 222 | Alex | 13 |
1 | Fire | In Progress | Mack | 223 | Alex | 13 |
1 | Fire | In Progress | Amy | 224 | Alex | 13 |
I want a summary table where it contains all the records from all 3 tables or a matrix where when expanding we get something like this or blank values for (Witness/ Person) if details are not present.
Incident_ID | Incident_Name | Status | Person_Name | Person_Staff_ID | Witness_Name | Witness_Staff_ID |
1 | Fire | In Progress | Jack | 222 | Alex | 13 |
|
|
| Mack | 223 |
|
|
|
|
| Amy | 224 |
|
Regards,
Ashfiya
Hi @Anonymous,
Your relationships should be:
Make sure that both the relationships have a single directional cross filter.
Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: Forecast Period - Previous Forecasts
Hi @Shaurya ,
Thanks for the reply, can you please elaborate more on Incident ID of which table,? as mentioned earlier the Incident ID ( Incident table is unique )
This cardinality is invalid.
1. Incident ID (Incident ) --1 to many --> Incident ID (Person )
2. Incident ID (Incident ) --1 to many --> Incident ID (Witness)
But when creating a table while adding all the columns from 3 tables I get the below error.