Filter on selected leaf node

[8a5c4b91-8100-4]

Hi,

I have a table with 2 columns: source and target. Sample:

Source	Target
Company	Plant A
Company	Plant B
Company	Plant C
Plant A	Building X
Plant A	Building Y
Plant B	Building R
Plant B	Building S

I created the column isLeaf to check for leaf nodes (True or False):

IsLeaf = 
    VAR CurrentNode = FullHierarchy[Target]
    RETURN CALCULATE (
        COUNTROWS ( FullHierarchy ),
        ALL ( FullHierarchy ),
        FullHierarchy[Source] = CurrentNode
    ) = 0

Also, the path is calculated using the PATH() function.

Next, I created the disconnected table Leafs containing only the leaf nodes:

Leafs = 
    SELECTCOLUMNS(
        FILTER(FullHierarchy, FullHierarchy[IsLeaf]),
        "LeafNode", FullHierarchy[Target]
    )

This is the result: 2 disconnected tables.

 

My goal is to have 2 visuals: a slicer based on Leafs and a table based on FullHierarchy. Selecting a leaf shows only the items that are directly or indirectly connected to it (i.e. part of it's path).

The statement to do so would be something like:

PATHCONTAINS(
    LOOKUPVALUE(FullHierarchy[Path],
                FullHierarchy[Target]
                [Selected Node]),
    FullHierarchy[Target])

The statement above would lookup the path for the selected leaf node, and then check if the node in the table was part of it, to decide whether or not to show it.

I've tried countless of variations, but have unable to implement this. Any ideas, suggestions, etc would be greatly appreciated! Thank you!

Sample pbix file is available here.

[v-yalanwu-msft · Accepted solution]

Hi, @8a5c4b91-8100-4 ;

You could create a flag measure.

flag = 
IF(CONTAINSSTRING(
CALCULATE(MAX('FullHierarchy'[P]),FILTER(ALL(FullHierarchy),PATHCONTAINS([P],MAX('Leafs'[LeafNode])))),
MAX('FullHierarchy'[Target])),1)

Then filter it .

The final result:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Your path is incomplete

After you add the top parent node this is the measure that you can use for your visual filter

See attached