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
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.
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.