Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Relationships between tables

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

Need Help

2 Replies

  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.