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. 

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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)