Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MakLihp
Frequent Visitor

DAX measure to check if matching value exists in another column

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:

 

ChildIdParentIdHierarchyLevelCurrent resultExpected result
1 top  
2 top  
31medium  
42medium  
511medium11
63low  
74low  
85low 1
912low11

 

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!

3 REPLIES 3
MakLihp
Frequent Visitor

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  
31medium  
42medium  
511medium11
63low  
74low  
85low 1
912low11

 

Thanks in advance!

Greg_Deckler
Community Champion
Community Champion

@MakLihp Can you not just use PATHCONTAINS?

PATHCONTAINS function (DAX) - DAX | Microsoft Docs



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@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?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.