DAX to display 2 levels of Hierarchy based on filter

[cmncp]

I have a hierarchy table that looks like this:

And a fact table like this:

And a simple model:

I have created a report with a Hierarchy Slicer and a Matrix as shown below.  But I also need to be able to display the table that is shown on the right.  In the sample shown, the user has selected "Node A.A" in the slicer, and the table needs to display the selected node ("Node A.A"), plus the 2 children ("Node A.A.A" and "Node A.A.B"), aggregating the data for them.  Is there a dax formula I can write that I can then use to filter this table?  Just to clarify, the requiement is to display the selected/filtered node and all children that are one level below.

Note, real model is build using SSAS Tabular 2017, so some functions are not available (such as IsInScope).

[az38]

@cmncp 

cannot you just remove Level3 and Level4 from hierarchy in visual fields pane

[cmncp]

Hi @az38 No, because I need to display the selected node and all children.  So in the example, the user selected Node A.A and therefore we display that node plus its immediate children (Node A.A.A and Node A.A.B).  If the choses Node A.A.B, then we need to display Node A.A.B and its immediate children (Node A.A.B.A and node A.A.B.B).  It is all relative to the selection the user makes in the slicer.

[Anonymous]

The best way to achieve this is by creating another table that will store 2 columns:
- a column with each node
- a column with each subnode that corresponds to the node in the first column plus the node from the first column. The table should be connected to Node Name in the first table (dimension with all the levels). The filtering would be 1:* from the hierarchy dimension to the said table. You would display the figures only when one node would be selected from the hierarchy dimension. A visual displays nothing if the column with a measure has blanks - use this feature to your advantage.

Best
D

[cmncp]

Hi @Anonymous Thanks for the suggestion, but I can't quite get it to work.  I have added the table like this:

And created relationship back to hierarchy table based on Children to NodeName:

But when I build my report and select the top "Node A" in my slicer, I still get all levels showing.  How do I restrict the table to just 2 levels?

 I have uploaded my PBIX file here: https://drive.google.com/open?id=13oxTuna3j9EYzvH7lkJb1dExq3k3U6oH 

[Anonymous]

Once you've got the correct setup, you'll write something like this:

// Say [Total Sales] works OK for any node.
//
// Relationships:
// Hierarchy[Node Name] 1 -> * Sales[Node Name]
// Hierarchy[Node Name] 1 -> * AllLevels[Parent]
//
// The measure that'll work with AllLevels:

[Node Sales] =
var __oneNodeVisible = HASONEVALUE( AllLevels[Parent] )
var __oneSubnodeVisible = HASONEVALUE( AllLevels[Children] )
var __shouldCalc =
	__oneNodeVisible && __oneSubnodeVisible
var __subnode = SELECTEDVALUE( AllLevels[Children] )
var __result = 
	CALCULATE(
		[Total Sales],
		Hierarchy[Node Name] = __subnode,
		ALL( Hierarchy )
	)
return
	if( __shouldCalc, __result )

Or something similar... You might need to discover which level the node belongs to and then make changes to __result accordingly.

Best

D

[Anonymous]

Or something like this:

// Say [Total Sales] works OK for any node.
//
// Relationships:
// Hierarchy[Node Name] 1 -> * Sales[Node Name]
// Hierarchy[Node Name] 1 -> * AllLevels[Parent]
//
// The measure that'll work with AllLevels:

[Node Sales] =
var __oneNodeVisible = HASONEVALUE( AllLevels[Parent] )
var __oneSubnodeVisible = HASONEVALUE( AllLevels[Children] )
var __shouldCalc =
	__oneNodeVisible && __oneSubnodeVisible
var __subnode = SELECTEDVALUE( AllLevels[Children] )
var __level = SELECTEDVALUE( AllLevels[Level] )
var __level1Result =
	CALCULATE(
		[Total Sales],
		Hierarchy[Level1] = __subnode,
		ALL( Hierarchy )
	)
var __level2Result =
	CALCULATE(
		[Total Sales],
		Hierarchy[Level2] = __subnode,
		ALL( Hierarchy )
	)
var __level3Result =
	CALCULATE(
		[Total Sales],
		Hierarchy[Level3] = __subnode,
		ALL( Hierarchy )
	)
var __level4Result =
	CALCULATE(
		[Total Sales],
		Hierarchy[Level4] = __subnode,
		ALL( Hierarchy )
	)
var __result = 
	switch( __level,
		1, __level1Result,
		2, __level2Result,
		3, __level3Result
		4, __level4Result,
		"This should not happen"
	)
return
	if( __shouldCalc, __result )

Best

D

[Anonymous]

Also, for this to work, the level in AllLevels must be the level of the child, not of the parent.

Best
D

[Anonymous]

I got it to work but I had to change the model. I've got the file on my OneDrive and I'll share as soon as possible.

Best
D

[Anonymous · Accepted solution]

Here's the link. Take a look at how I create the LxToLx tables and how the Parent Level and Child Level are used in the measure.

https://1drv.ms/u/s!ApyQEauTSLtOgYJQZeQffdQS0PwRTg?e=XABEbl

Best
D

[cmncp]

@AnonymousThat's awesome work.  Let me have a play around with it today.  Thanks for going to all that effort.

[Anonymous]

Hi there. It's the Parent that has to be connected to Node Name, not the children. Once this is done, then selecting a single node in Hierarchy will give you in AllLevels the children of the node and the node itself (in the Children column). You also have to have a measure that will work with AllLevels in such a way that it'll return the values for the visible Children when only one node is selected in Hierarchy. All the other rows will receive BLANK. This way your table created from Children and the measure will show what you want.

Bear in mind, though, that the measure you'll be creating to achieve this will have to rely on virtual relationships and TREATAS is your friend. If you can't use TREATAS, you'll  be relying on INTERSECT. The filtering should be from Hierarchy to AllLevels in a 1:* fashion (no cross-filtering as you don't need this at all and it would be confusing to say the least).

I'm at work and can't therefore open or even download files from a Google Drive. Could you please place the file somewhere else? Maybe OneDrive?

Best
D