Forum Discussion

sjehanzeb's avatar
sjehanzeb
Icon for Resolver I rankResolver I
5 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • sjehanzeb's avatar
    sjehanzeb
    5 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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 Adaptive

 

Pat

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    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