Forum Discussion

8a5c4b91-8100-4's avatar
8a5c4b91-8100-4
Regular Visitor
3 years ago
Solved

Filter on selected leaf node

Hi,

 

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

 

SourceTarget
CompanyPlant A
CompanyPlant B
CompanyPlant C
Plant ABuilding X
Plant ABuilding Y
Plant BBuilding R
Plant BBuilding 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.

 

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

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support

    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.