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