Using Path to filter table showing only PathContains Data

[sjehanzeb]

The data contains the following table

Name	Parent
A
B	A
C	B

The objective is to have below two slicers, a slicer 

1. to filter data based on the 'Name' selected (simple enough)

2. to filter data of all 'Name' which are under its 'Path'  [the issue]

Path=Path(Name, Parent)

the data becomes, 

Name	Parent	Path
A		A
B	A	A|B
C	B	A|B|C

I create a calculated table with filtering using pathcontains

NewTable = FILTER(CopyofTable, PATHCONTAINS([Path], SelectedValue[DisconnectedName]))

I have developed a disconnected name table for reference purposes

The above is not working. The objective is to filter the original table with based on their name being in the path

[sjehanzeb · Accepted solution]

Ok. I was able to resolve this

1. Created a disconnected table of the original table, lets call it tableB

2.  created a calculated column 'Path' in tableB

3. created an additional calculated table tableC, with union of 4 levels of pathitems eg. 

var _table = SUMMARIZE(tableB, Name,Parent,Path)
var _table1 = UNION(
    ADDCOLUMNS(_table, "Entity",  PATHITEM(Path, 1, TEXT)),
    ADDCOLUMNS(_table, "Entity",  PATHITEM(Path, 2, TEXT)),
    ADDCOLUMNS(_table, "Entity",  PATHITEM(Path, 3, TEXT)),
    ADDCOLUMNS(_table, "Entity",  PATHITEM(Path, 4, TEXT))
)
return
SUMMARIZE(_table1, [Entity],Name)

4. Created a bidirectional relationship between tableC 'Name' and tableA 'Name'

5. Using Entity as Slicer, I was able to create filter childs of that group. 

[mahoneypat]

You can use a measure like this one to get that result.  Note that I used VALUES instead of SELECTEDVALUE, so more than one leader can be selected at a time, if needed.  You can use it in a visual like shown, or you can use it as a visual level filter with >0 condition.

ParentIsSelected =
VAR thisparent =
    VALUES ( Names2[Name] )
VAR result =
    SUMX (
        thisparent,
        COUNTROWS ( FILTER ( Names, SEARCH ( Names2[Name], Names[Path], 1, 0 ) > 0 ) )
    )
RETURN
    result

Also, you can see this article for a more robust way to generate your PATH in the query editor vs. a DAX column, which requires the top parent be present in the name column (e.g., A reports to A in your example). 

Guest Post: Using List.Accumulate for Input/Output Genealogy – The BIccountant

You can also see this article on P3 adaptive about the PATH function.

On the Right PATH() - A DAX/Power Query approach to working with parent/child hierarchies. | P3 Adap...

Pat

[PrabhavatiMali]

@mahoneypat this was really helpful to count the children of a parent using path.

I am stuck in a scenario where I have to count layers under a parent and show them in a card visual when a emp name is selected in slicer.

I can't use ISINSCOPE as it is working when I'm showing layers along with hierarchy and shows overall hierarchy layers total in a card which is not helpful.

I gave a try using PATHCONTAINS but it is not grouping the blank fields and resulting showing wrong layers for employees.

Name   Path   Expected # layers

     A        A            1

     A        A|B         2

     A        A|C         2
     A       A|B|D       3

Here A is at level1 so layer is counted as 1. B and C are falling at level 2 so the layers beome 2 for A and so on.

Measure 1 = PATHLENGTH(Table2[PATH]))

Measure 2 = 
var EntityRowDepth = MAX(Table1[Measure 1])
var EntityBrowseDepth =
PATHCONTAINS(Table2[PATH]),MAX(Table2[LEVEL1]))+
PATHCONTAINS(Table2[PATH]),MAX(Table2[LEVEL2]))+
PATHCONTAINS(Table2[PATH]),MAX(Table2[LEVEL3]))
RETURN EntityRowDepth-EntityBrowseDepth+1

Thanks in advance.

[mahoneypat]

Not totally clear on your scenario. If you have a single path in scope, you can use something like

= PATHLENGTH(MIN(Table[Path]))

If you have multiple paths in scope and need the longest one, you can use

= MAXX(DISTINCT(Table[Path]), PATHLENGTH(Table[Path]))

Pat

[Greg_Deckler]

@sjehanzeb That won't work because calculated tables are not dynamic, they only recalculate at data load or refresh. I would recommend two non-connected tables and a PATHCONTAINS measure. So let's say:

Table

Name  Parent  Path

A                      A

B          A          A|B

C          B          C|B|A

Slicer2

A

B

C

Slicer1 is based off of Table. Create this measure and use it as a filter for your Slicer2 slicer:

Selector Measure =
  VAR __Value = __SELECTEDVALUE('Slicer2'[Column1])
RETURN
  IF(PATHCONTAINS('Table'[Path], __Value),1,0)