Relationships between tables

[Anonymous]

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

[Shaurya]

Hi @Anonymous,

Your relationships should be:

1. Incident ID -- * to 1 -- Person
2. Incident ID -- * to 1 -- Witness

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

[Anonymous]

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 )

1. Incident ID (incident) -- * to 1 -- Person
2. Incident ID (incident) -- * to 1 -- Witness

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.