Hierarchical Data Visualisation

[StephenRabobank]

Hello,

I have a work tracking system (AzDO) so we have Product Backlog Items that have a dependency between them. We are looking to have a simple view of “this PBI links to this(these) PBI(these)” so not going one more level of “this PBI links to this(these) PBI(s) which then links to this (these) PBI(s)”

As this relationship is 1:n I cannot put the dependency linkage into the PBI table, so the simplified view is this

WorkItemID	State	Target Date
123	Open	15-04-2025
124	Open	17-04-2025
223	Open	20-04-2025
224	Open	30-04-2025
331	Open	15-05-2025

The relationship is also simple and points to another record in the same table

WorkItemID	TypeOfRelation	Target_WorkItemID
123	Predecessor	124
124	Predecessor	223
124	Predecessor	224
224	Predecessor	331

So in the end, we get a table (if I did a Left Join) of this

WorkItemID	State	TargetDate	D.WorkItemID	D.State	D.TargetDate
123	Open	15-04-2025	124	Open	17-04-2025
124	Open	17-04-2025	223	Open	20-04-2025
124	Open	17-04-2025	224	Open	30-04-2025
223	Open	20-04-2025
224	Open	30-04-2025	331	Open	15-05-2025
331	Open	15-05-2025

In the case where I have a "parent-child" relationship (think a Feature has 1:n PBIs) the Matrix visual works to get my desired view. Using the data shown it would be this:

WorkItemID	State	TargetDate	D.WorkItemID	D.State	D.TargetDate
123	Open	15-04-2025	124	Open	17-04-2025
124	Open	17-04-2025	223	Open	20-04-2025
			224	Open	30-04-2025
223	Open	20-04-2025
224	Open	30-04-2025	331	Open	15-05-2025
331	Open	15-05-2025

However, since the PBIs relate to things in the same table I cannot figure out how to do this without building another table, which would then result in the output shown in the left join table above. 

I'm willing to accept that it is just not possible and the users will need to see things repeated, but I'm trying to avoid that. I also know if may be possible with a lot of effort on the model side with indexes and checking to see if the prior row value is the same, but I'm trying to save cycle times. If there is a visualisation that exists that can do it, that would be great. But most that I've seen in the marketplace are things where an entry has one parent which is not the case here.

Thanks in advance.

[bhanu_gautam]

@StephenRabobank Go to the "Model" view in Power BI and create a relationship between the WorkItemID in the PBIs table and the WorkItemID in the Dependencies table.

Use DAX to create a new table that combines the PBIs and Dependencies tables. You can do this by using the GENERATE function to create a Cartesian product of the rows.

DAX
CombinedTable =
GENERATE(
PBIs,
FILTER(
Dependencies,
Dependencies[WorkItemID] = PBIs[WorkItemID]
)
)

Add a Matrix visual: In the "Report" view, add a Matrix visual to your report.

Configure the Matrix:

Rows: Add WorkItemID, State, and TargetDate from the PBIs table.
Columns: Add Target_WorkItemID, TypeOfRelation, and TargetDate from the Dependencies table.

[StephenRabobank]

Hello,

I attempted the solution and it did not work.

I guess what I'm also not groking is what is the purpose of the generated table. There wasn't an indication if i were to link it to somthing as a relationship, nor use it in the matrix as you described. 

If the goal is to create a smaller PBI table based upon the values of the target and do a relationship, and the column values you indicated would come from the newly created table I can understand it. But we'd then end up with another problem as the target work item could be the target of multiple PBIs.

That is item 125 predecessor 331 in the case where two different work streams need to come together to one target.

[v-bmanikante]

Hello @StephenRabobank ,

Thank you for reaching out to Microsoft Fabric Community forum.

@bhanu_gautam Thank you for your response.

You're right to question the purpose of the generated table. The goal behind using GENERATE was to create a flat, denormalized view combining the Work Items with their dependencies. But from your explanation and the error (WorkItemID already exists), it looks like there's a column name conflict, likely because both tables share a column with the same name.

Here’s a clarified approach you can try:

You can try as below:

Rename Columns During Table Creation
Instead of directly pulling the same column names, use SELECTCOLUMNS to control naming and avoid duplicates:
CombinedTable =
GENERATE(
    ADDCOLUMNS(
        PBIs,
        "PBI_ID", PBIs[WorkItemID],
        "PBI_State", PBIs[State],
        "PBI_TargetDate", PBIs[TargetDate]
    ),
    FILTER(
        Dependencies,
        Dependencies[WorkItemID] = PBIs[WorkItemID]
    )
)

Now, in the Matrix visual, use:

Rows: PBI_ID, PBI_State, PBI_TargetDate

Columns or Values: Dependencies[Target_WorkItemID], or bring in additional dependency details as needed.

          Since this new table is already a merged view, you don’t need to create additional relationships. Use it directly in the visual.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @StephenRabobank ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[StephenRabobank]

At issue is that still throws the same error as i use your suggestion.

You mention the use of SELECTCOLUMNS but it's not in the suggestion. Additionally, given that the ADDCOLUMNS appears to only add data from the PBI table, I do not see how/where the dependency table comes from. The example from MS shows that I'm adding to the existing table other related table columns. So in that case it's creating a summarisation of the data. 

Because of this I cannot use the example or begin to figure out how to expand from the hint.

[v-bmanikante · Accepted solution]

Hello @StephenRabobank ,

The earlier suggestion using ADDCOLUMNS didn’t pull in the dependency details , that was an oversight. Here's the corrected approach, which avoids column name conflicts and gives you a clean, report-ready table.

CombinedTable =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( PBIs, Dependencies ),
        PBIs[WorkItemID] = Dependencies[WorkItemID]
    ),
    "WorkItemID", PBIs[WorkItemID],
    "State", PBIs[State],
    "TargetDate", PBIs[TargetDate],
    "RelatedWorkItemID", Dependencies[Target_WorkItemID],
    "RelationType", Dependencies[TypeOfRelation]
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @StephenRabobank 

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @StephenRabobank ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[StephenRabobank]

Ok, I was concerned that creating a DAX table on the fly would have major performance issues. But I will try out the suggestion and see what the performance looks like.

The way you created the Matrix table is slightly concerning. Given the relationship you are suggesting is similar to the feature->pbi table, this generated a lot of columns. Or did you mean to the "Values" well in the Matrix not the "Column" well?

If you meant that it should be in the "values" segment of the matrix, it only allows for first (earliest/{math}), last(latest/{match}), count values. So if there is two then it wouldn't display both.

I was thinking that the Rows would be WorkItemID, State, Target Date, and Target_WorkItemID with the values being first TypeofRelation and Earliest TargetDate in the value well.

If there is something i'm misunderstanding please let me know. I'll try this out and if it works i'll mark it as the solution.

thank you for being quick and helpful.