topic Re: DAX measure to check if matching value exists in another column in DAX Commands and Tips

DAX measure to check if matching value exists in another column

MakLihp — Wed, 18 May 2022 15:07:33 GMT

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!

Re: DAX measure to check if matching value exists in another column

Greg_Deckler — Wed, 18 May 2022 15:24:17 GMT

MakLihp Can you not just use PATHCONTAINS?

PATHCONTAINS function (DAX) - DAX | Microsoft Docs

Re: DAX measure to check if matching value exists in another column

MakLihp — Wed, 25 May 2022 07:09:57 GMT

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?

Re: DAX measure to check if matching value exists in another column

MakLihp — Tue, 31 May 2022 07:45:35 GMT

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!