Parent Child Relationship PATH and Visual

[daxdaxdax]

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

[amitchandak]

@daxdaxdax , refer if these blog can help

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

[OwenAuger]

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

[daxdaxdax]

@OwenAuger  :  Exactly. That is my requirement ! 

Could you please assist with sample file?

[OwenAuger · Accepted solution]

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