Hi everybody,
I am working with a hierarchical dataset like the sample below in a dynamically filtered visual. As filters are applied to the visual, I want only the children that have an (indirect) hierarchical connection to a top level parent to be shown.
To check whether each ParentId matches with a ChildId, I currently use the following measure:
Current measure =
var parentid = VALUES(Sheet1[ParentId])
//Defines logic for evaluating if child is matched to parent or not:
var matches =
IF(
CALCULATE(
COUNTROWS(Sheet1),
ALLSELECTED(),
TREATAS(parentid,Sheet1[ChildId])
),BLANK(),1
)
return
//Perform logic for each row in table, except if the hierarchy level equals top:
SUMX(FILTER(Sheet1, Sheet1[HierarchyLevel]<>"Top"),matches)
It almost works. The problem is children such as ChildId 8, which have a parent (5) but this parent is not connected to the top level. See the difference between Current result and Expected result below:
| ChildId | ParentId | HierarchyLevel | Current result | Expected result |
| 1 | | top | | |
| 2 | | top | | |
| 3 | 1 | medium | | |
| 4 | 2 | medium | | |
| 5 | 11 | medium | 1 | 1 |
| 6 | 3 | low | | |
| 7 | 4 | low | | |
| 8 | 5 | low | | 1 |
| 9 | 12 | low | 1 | 1 |
Intuitively, I think the easiest solution would be to just have the measure run the same logic twice: first (1) checking if there is a matching ChildId in whole table (as is currently done), second (2) checking if there is a matching ChildId in a table filtered by the results of the first check.
However, I cannot seem to get this working, nor do I know if it is the right solution at all.
Does anyone know a solution? Any help would be greatly appreciated!
