Forum Discussion
Using Path to filter table showing only PathContains Data
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
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.
5 Replies
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)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
resultAlso, 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.
Pat
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 3Here 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.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
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.
