Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Will just post this here again to see if anyone knows how to get the expected result using a DAX measure:
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 |
Thanks in advance!
@MakLihp Can you not just use PATHCONTAINS?
PATHCONTAINS function (DAX) - DAX | Microsoft Docs
@Greg_Deckler Thanks for your reply! Maybe PATHCONTAINS works, but then I still wouln't know how to get the Expected result instead of Current result as described in my post. Any ideas?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |