Forum Discussion
Unique Parent/Child or Orphan
You can work with the current table structure, but organizing the data as you described may require additional calculated columns or measures to effectively group the Parent and Child opportunities.
You can create a calculated column or measure that identifies the Parent ID for each Child. This will help you group the Children under their respective Parents in the table visual.
Create a calculated column to establish a display order. For instance, you can assign a numeric value to Parents and their Children based on their relationship, ensuring the Parent is listed first, followed by its Children.
ParentDisplayName = IF( [Type] = "Parent", [ID], [ParentID] ) DisplayOrder = IF( [Type] = "Parent", 1, 2 )Use the ParentDisplayName in your table to group Children under their respective Parents. Sort the table by ParentDisplayName and then by DisplayOrder to ensure Parents are listed first, followed by their Children.
For Orphans, since they don't have a ParentID, they will appear on their own, which you can manage through sorting and grouping.
hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
Memorable Memberhi, mabruzzo
First, you’ll need to create a relationship table that defines which opportunities are children of which parents. This table could look something like this:
ParentOpportunityID ChildOpportunityID
------------------- -----------------
P1 C1
P1 C2
P2 C3
In Power BI, import your main Opportunities table and this relationship table. Set up a relationship between Opportunities and the Parent-Child relationship table on the OpportunityID and ChildOpportunityID.
You can create calculated columns to help with organizing your data. For example:
IsParent: A calculated column to check if an opportunity is a parent.
IsParent =
IF(
COUNTROWS(
RELATEDTABLE(ParentChildTable)
) > 0,
"Yes",
"No"
)
IsOrphan: Another calculated column to determine if an opportunity is an orphan.
IsOrphan =
IF(
ISBLANK(
RELATED(ParentChildTable[ParentOpportunityID])
) && [IsParent] = "No",
"Yes",
"No"
)
Create a new table visual in Power BI. Add the ParentOpportunityID and ChildOpportunityID fields to this table. You can group by ParentOpportunityID and then show the children opportunities underneath.
For orphans, you can add a separate table or include a column in your main table visual that filters out only those opportunities that do not have a parent.
hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
Do i need a separate table to do this? Currently my table has and ID, type (Parent, Child, Orphan), IsParent (T/F), & ParentID. Can I utilize this or should I break it out into a separate table?
- hackcrr
You can work with the current table structure, but organizing the data as you described may require additional calculated columns or measures to effectively group the Parent and Child opportunities.
You can create a calculated column or measure that identifies the Parent ID for each Child. This will help you group the Children under their respective Parents in the table visual.
Create a calculated column to establish a display order. For instance, you can assign a numeric value to Parents and their Children based on their relationship, ensuring the Parent is listed first, followed by its Children.
ParentDisplayName = IF( [Type] = "Parent", [ID], [ParentID] ) DisplayOrder = IF( [Type] = "Parent", 1, 2 )Use the ParentDisplayName in your table to group Children under their respective Parents. Sort the table by ParentDisplayName and then by DisplayOrder to ensure Parents are listed first, followed by their Children.
For Orphans, since they don't have a ParentID, they will appear on their own, which you can manage through sorting and grouping.
hackcrr
If I have answered your question, please mark my reply as solution and kudos to this post, thank you!