Forum Discussion
Parent Child Relationship PATH and Visual
Hi Community,
I am trying to create a power BI dashboard with data that has portfolio, parent and child.
In the data, there are two columns for this projectcode and parent code
For sample:
I have used PATH function to identify the parent-child relationship and created a new column.
Now, I am unsure how i can have all the visuals in the dashboard to reflect the parent child relation?
I want to have a parent project slicer on the top of the dashboard and it should change all the visuals showing the details of child projects. I want to ignore the portfolio level.
I am unsure how to achieve this? Currently, I cannot see any effect of the PATH function in my dashboard i.e. no portfolio-parent or parent-child realtionship is being reflected
Please assist?
Thanks
Hi again daxdaxdax
No problem, I have attached a sample PBIX 🙂
I actually created two different methods in this file, but I will describe the first one which uses PATHCONTAINS, which is on the first page of the report.
1. The relevant tables:
Portfolio
Parent Filter
Unique projectcode values, used for selecting parent(s).
Include Parent (parameter)
This was created using the Parameter function, and the intention is to allow the user to include/exclude Parent on the fly.
The values are formatted as:
0 = Exclude Parent
1 = Include Parent
2. Measures
The most important measure is Project Count with Parent Filter.
The measure returns the count of Projects that are descendants of the Parent(s) selected from the Parent Filter table.
Include Parent Value = SELECTEDVALUE ( 'Include Parent'[Include Parent], 0 )Project Count = COUNTROWS ( Portfolio )Project Count with Parent Filter = -- Get Include Parent parameter value -- Include = 1 -- Exclude = 0 VAR IncludeParent = [Include Parent Value] -- Get the Project selection from the disconnected 'Parent Filter' VAR ParentSelection = VALUES ( 'Parent Filter'[projectcode] ) -- Get the filtered values of Project & Path VAR ProjectPathValues = SUMMARIZE ( Portfolio, Portfolio[projectcode], Portfolio[Path] ) -- Filter Project/Path to those that are descendants of Parent -- taking into account "Include Parent" parameter VAR ProjectPathFilter = FILTER ( ProjectPathValues, VAR ParentInPath = NOT ISEMPTY ( FILTER ( ParentSelection, PATHCONTAINS ( Portfolio[Path], 'Parent Filter'[projectcode] ) ) ) VAR ParentFlag = OR ( NOT Portfolio[projectcode] IN ParentSelection, IncludeParent ) RETURN AND ( ParentInPath, ParentFlag ) ) -- Return Project Count with the filter applied RETURN CALCULATE ( [Project Count], KEEPFILTERS ( ProjectPathFilter ) )3. Apply the measure to filter visuals
The Project Count with Parent Filter measure can be applied in two ways:
(a) As a visual level filter for visuals that are grouped by projectcode. The measure is filtered to >= 1.
(b) By displaying the measure itself in a visual.
For example, if we select Parent = 124 and choose "Exclude Parent", then visuals with this filter applied will show 125, 126, 127, 128:
I hope that's along the lines of what you are looking for! It will no doubt need some tweaking for your model.
Please post back if needed 🙂
Regards
6 Replies
Hi daxdaxdax
Could you clarify the exact behaviour you would like?
From what you've described, if you select a particular "Parent", would you like the report to be filtered to all descendants of that Parent (direct or indirect) but excluding the Parent itself - is that correct?
For example
- If you selected Parent = 123, would you want the report to be filtered to all Projects except 123 (since they are all direct/indirect descendants of 123)?
- If you selected Parent = 124, would you want the report to be filtered to 125, 126, 127 & 128, but not 124 itself?
For this type of requirement, you can apply a filter by making use of the PATHCONTAINS function. You could alternatively create a table that maps a Parent to its descendants.
I can send back and example PBIX after confirming the requirement.
Regards
Hi again daxdaxdax
No problem, I have attached a sample PBIX 🙂
I actually created two different methods in this file, but I will describe the first one which uses PATHCONTAINS, which is on the first page of the report.
1. The relevant tables:
Portfolio
Parent Filter
Unique projectcode values, used for selecting parent(s).
Include Parent (parameter)
This was created using the Parameter function, and the intention is to allow the user to include/exclude Parent on the fly.
The values are formatted as:
0 = Exclude Parent
1 = Include Parent
2. Measures
The most important measure is Project Count with Parent Filter.
The measure returns the count of Projects that are descendants of the Parent(s) selected from the Parent Filter table.
Include Parent Value = SELECTEDVALUE ( 'Include Parent'[Include Parent], 0 )Project Count = COUNTROWS ( Portfolio )Project Count with Parent Filter = -- Get Include Parent parameter value -- Include = 1 -- Exclude = 0 VAR IncludeParent = [Include Parent Value] -- Get the Project selection from the disconnected 'Parent Filter' VAR ParentSelection = VALUES ( 'Parent Filter'[projectcode] ) -- Get the filtered values of Project & Path VAR ProjectPathValues = SUMMARIZE ( Portfolio, Portfolio[projectcode], Portfolio[Path] ) -- Filter Project/Path to those that are descendants of Parent -- taking into account "Include Parent" parameter VAR ProjectPathFilter = FILTER ( ProjectPathValues, VAR ParentInPath = NOT ISEMPTY ( FILTER ( ParentSelection, PATHCONTAINS ( Portfolio[Path], 'Parent Filter'[projectcode] ) ) ) VAR ParentFlag = OR ( NOT Portfolio[projectcode] IN ParentSelection, IncludeParent ) RETURN AND ( ParentInPath, ParentFlag ) ) -- Return Project Count with the filter applied RETURN CALCULATE ( [Project Count], KEEPFILTERS ( ProjectPathFilter ) )3. Apply the measure to filter visuals
The Project Count with Parent Filter measure can be applied in two ways:
(a) As a visual level filter for visuals that are grouped by projectcode. The measure is filtered to >= 1.
(b) By displaying the measure itself in a visual.
For example, if we select Parent = 124 and choose "Exclude Parent", then visuals with this filter applied will show 125, 126, 127, 128:
I hope that's along the lines of what you are looking for! It will no doubt need some tweaking for your model.
Please post back if needed 🙂
Regards
Hi OwenAuger
I found your very helpful response, I'm really struggling with the same issue. I think your solution solves my challenges - but one question still remains: Do you know what causes this error message:
I downloaded your pbix and I don't understand why I receive error, even if my table structure is the same than yours. Any hints to solve this? It would help a lot 😊
-katina
