How to count downstream impacts from table containing list of relationships?

[Glassford]

I have what I assume is a simple problem, but cannot find the answer for the life of me.

Problem

From a list of incident records contained in 1 table, I need to search a related table which contains relationship mappings for downstream impacts.

Tables

• Table 1:
  • List of incident numbers (unique key) and incident details.
• Table 2:
  • List of all relationships between records, such as incidents, problems and changes.

Table 1 example data:

Incident Number	Date
IM123456	9/5/2022
IM987654	10/5/2022

Table 2 example data:

Source	Related Record	Relationship Type
IM123456	C654321	Resolved by Change
C654321	IM987654	Caused Incident
IM987654	C321654	Resolved by Change

From the example data above, I want to count how many relationships are downstream from "IM123456". From a manual calculation, I can see the number should be 3, I need to recreate this in Power BI.

I have tried various ways to make the table relationship, but none have worked out.

Thanks in advance.

[Arul]

@Glassford ,

Can you explain your question a bit more?

Thanks,

Arul

[Glassford]

So from table 1, I am trying to count flow on impacts.

For the first entry of table 1, IM123456, in table 2, I can see the following flow on impact:

IM123456>>Resolved by Change>>C654321>>Caused Incident>>IM987654>>Resolved by Change>>C321654

For the second entry, the flow on will be:

IM987654>>Resolved by Change>>C321654

Essentially, I want to see what records from table 1 relate to records in table 2 (already solved). Then see what records in table 2 have further downstream relationships which are also stored in table 2.

Another way to put it, I am trying to find which entries in [Table 2.Related Records] are also in [Table 2.Source], then iterate that till no more downstream records are found.