Forum Discussion

sjehanzeb's avatar
sjehanzeb
Resolver 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. 

     

Anonymous's avatar
Anonymous
Not applicable
3 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
Microsoft Employee
3 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