topic Re: How to make hierarchical path use visual filters in DAX Commands and Tips

How to make hierarchical path use visual filters

DSenkovic — Tue, 22 Jun 2021 15:34:50 GMT

Hi everyone,

I have a table which contains some hierarchical data with id and parentid fields among others fields.

I can create appropriate structure with PATH (and other path related DAX functions). I can also display it visually with level hierarchy in Hierarchy Slicer.

Each row in this table additionally has certain numeric field. What I need to determine is the value of this field for the top-most selected (not filtered-out) parent leaf for each row.

So, for example, for tree like this with this significant numeric value written in parentheses next to node names:

N(5)

/ \

N1(7) N2(8)

/ \ / \

N11(4) N12(6) N13(2) N14(9)

If all nodes are selected in a slicer, I need to get value (5) for all rows (in some new measure or column) as the value, since 5 is the field value of top-most parent.

If N1, N11 and N12 are the only ones selected then all three of them should have (7) as a value for this new field.

If on the other hand N13 is the only one selected then it should have (2) as a value for this field.

I am able to acchieve this with PATH related functions but I cannot make it work with filters applied. It always works on an entire dataset and always returns topmost value (5) for all rows.

P.S. This should also work with URL filtering when table rows are filtered by external URL parameters when embedding powerBI report in a web page.

Any help is appreciated. Thank you very much.

Re: How to make hierarchical path use visual filters

OwenAuger — Wed, 23 Jun 2021 04:53:34 GMT

Hi DSenkovic

Here's an initial idea - PBIX attached.

I'm working on the assumption that you have a data model with:

Nodes table, with columns Node, Parent and Path (created using PATHITEM)
NodeValue table containing Node and Value columns.
Relationship between NodeValue[Node] and Nodes[Node]

Path = PATH ( Nodes[Node], Nodes[Parent] )

Then you can create a measure like the one below (note that this can most likely be optimized).

Assumptions I made in writing the measure:

You only want the measure evaluated for a single node at a time.
The measure calculates the value sum for the topmost node in the path for the currently filtered node, within the overall selection of nodes (using ALLSELECTED).

Topmost node value = VAR CurrentNode = SELECTEDVALUE ( Nodes[Node] ) RETURN IF ( NOT ISBLANK ( CurrentNode ), -- Only return result if single node selected VAR CurrentNodePath = -- Get path of current node SELECTEDVALUE ( Nodes[Path] ) VAR CurrentNodePathLength = PATHLENGTH ( CurrentNodePath ) VAR CurrentNodePathTable = -- Table containing Node/Position pairs for CurrentNodePath SELECTCOLUMNS ( GENERATESERIES ( 1, CurrentNodePathLength ), -- Table of values {1,2,...,CurrentNodePathLength} "@Node", PATHITEM ( CurrentNodePath, [Value] ), "@Position", [Value] ) VAR VisibleNodesAllSelected = -- List of all nodes selected overall CALCULATETABLE ( VALUES ( Nodes[Node] ), ALLSELECTED () ) VAR CurrentNodePathTableFiltered = -- Filter path to just those nodes selected overall FILTER ( CurrentNodePathTable, [@Node] IN VisibleNodesAllSelected ) VAR TopNode = -- Get the top Node (i.e. with minimum position in the path) -- and treat as Nodes[Node] value TREATAS ( SELECTCOLUMNS ( TOPN ( 1, CurrentNodePathTableFiltered, [@Position], ASC ), "@TopNode", [@Node] ), Nodes[Node] ) RETURN CALCULATE ( [Value Sum], TopNode, ALL ( Nodes ) ) )

For example, if you select N1, N11, N12, N2, N14, then:

N1, N11 and N12 have Topmost node value = 7
N2 and N14 have Topmost node value = 8

Hopefully this is of some use and can be adapted to your model.

Regards,

Owen

Re: How to make hierarchical path use visual filters

DSenkovic — Wed, 23 Jun 2021 12:52:31 GMT

Hey Owen,

Thank you so much, that solved the problem. I lost two days jumping around it. You rock! 🙂

Best,

D. Senkovic

Re: How to make hierarchical path use visual filters

DSenkovic — Wed, 23 Jun 2021 23:00:53 GMT

OwenAuger Thank you for your help so far. Just a quick question.

If I had a new (child) table to the Node table - let's call it a Leaf table. It has foreign key to node_id of course among other fields. Any Node can have any number of Leaves.

If I needed to display the Topmost node measure value in the visual that displays the Leaves data of selected Nodes, I suppose that the measure would need to be changed in a way that SELECTEDVALUE is no longer the way to get the current node for the Leaf. I should somehow use the Node from the filter context of the Leaf.

Is that correct way of thinking? Thank you.

Re: How to make hierarchical path use visual filters

OwenAuger — Mon, 28 Jun 2021 10:46:04 GMT

You're welcome! 🙂
I just managed to get back to looking at this.

The exact way of handling this depends on your data model setup.

I have attached an updated PBIX using option 1 below, with a NodeChild table added.

You could set up the model with the NodeChild table having a bidirectional relationship with the Node table. This would mean the filter on a Child would propogate to the corresponding node.

There would just be a small tweak required to the measure near the end, changing ALL ( Nodes ) to ALLCROSSFILTERED ( Nodes ). This is because we need to remove all filters affecting the Nodes table, including those on the many-side of a relationship with Nodes.

I have used this method in the attached PBIX.
If you don't have a bidirectional relationship between Nodes and NodeChild, then you need to use DAX to filter Nodes based on NodeChild. There are different ways to do this, but one is to wrap the SELECTEDVALUE calls in CALCULATE with NodeChild as a filter argument, so you end up with code like below:

VAR CurrentNode = CALCULATE ( SELECTEDVALUE ( Nodes[Node] ), NodeChild ) VAR CurrentNodePath = CALCULATE ( SELECTEDVALUE ( Nodes[Path] ), NodeChild )

Hope this helps!

Regards,

Owen

Re: How to make hierarchical path use visual filters

DSenkovic — Mon, 28 Jun 2021 20:15:12 GMT

Thank you!