Forum Discussion
Hierarchical Data Visualisation
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
Super UserStephenRabobank 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.
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.
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.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
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
