Search in a tree hierarchy visual

[janisska]

Dear community,

I'm really stuck and could really use some advice. I have a source table with already existing multilayer parent-child hierarchy.

I have tried different visuals, but for example the decomposition tree doesn't work for my needs and the easiest and best one seems to be the Tree by ck corporation, because it already works with the parent-child hierarchy and no separating columns was necessary (although I have tried but the issue with the search was still there, just a different one).

I have a big amount of data, so I would really need a typing search slicer.

The visual looks great, but I need to be able to search as there is just too much values, but I can't figure out how to do it, because when I use a slicer to filter a name, the hierarchy dissapears and there is only the one value, but I would like to see the whole structure concerning that particular name (meaning all parents and all children).

I have tried different DAX approaches but nothing has worked for me.

I will be grateful for any advice. Thanks in advance!

[Sahir_Maharaj]

Hello @janisska,

Can you please try the following:

1. Create a calculated column for the Path

PathColumn = PATH(Table[NodeID], Table[ParentID])

2. Create Measure for Node Search

SelectedNodeSearch = 
VAR SelectedNode = SELECTEDVALUE('NodeTable'[NodeName])
RETURN
IF(
    CONTAINSSTRING(Table[PathColumn], SelectedNode),
    1,
    0
)

3. Create Measure for Showing Parent and Child Nodes

ShowRelatedNodes = 
IF(
    [SelectedNodeSearch] = 1,
    1,
    0
)

In your Tree visual, add the ShowRelatedNodes measure as a visual-level filter and set it to show only when the value equals 1 (ensures that when a node is selected, the entire hierarchy structure (including parents and children) remains visible.)

Hope this helps.

[janisska]

Hi @Sahir_Maharaj, thanks for your reply. I have already tried this with chat gpt (as well as many other solutions), here I get an error with the SelectedNodeSearch.

"A single value for column 'PathColumn' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[suparnababu8]

Hi @janisska 

You may use Hirearchy slicer for your need. 

If you need additional information pls go through this https://data-mozart.com/finding-the-right-path-understanding-parent-child-hierarchies-in-power-bi/   

[janisska]

Hi @suparnababu8, thanks for your reply. I tried it and this slicer works nice with the decomposition tree, however for the parent-child hierarchy in my Tree from ck corporation it doesn't.