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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ale
Resolver II
Resolver II

Check whether a parent has children in Hierarchy

My problem is the following: I have a dataset with a Hierarchy of 7 levels, and a column to indicate if that element is an "Intermediate" or "End" of the hierarchy. If it's Intermediate, this item MUST have a child. However, I found out that in my real data, there are a few cases where this does not happen. As you can see in the image below, the item in red (Column ID 15) is an Intermediate but has no child. That means the data is coming wrong and I want to flag it.

 

Screenshot Hierarchy.PNG

 

 

 

 

 

 

 

What I need is exactly a function that would flag every case where that happens. If the row is intermediate and has no child, flag as "Error". Ideally, this should come as an extra Column, but if not possible, a Measure might be ok as well.

 

How can I solve that? (the real data is not sorted like that, I just sorted it in Excel to make it easier to understand).

 

Column IDPathTree DepthIntermediateOrEndParentValueLevel1Level2Level3Level4Level5Level6Level7
11493921|13232462Intermediate1493921 14939211323246     
21493921|1323246|21091613End13232465149392113232462109161    
31493921|1323246|591543End132324671493921132324659154    
41493921|1323246|15715563End13232462149392113232461571556    
51493921|1323246|8092143Intermediate1323246 14939211323246809214    
61493921|1323246|2177863Intermediate1323246 14939211323246217786    
71493921|1323246|809214|15715564End8092147149392113232468092141571556   
81493921|1323246|217786|591544End21778691493921132324621778659154   
91493921|1323246|809214|591544End80921441493921132324680921459154   
101493921|1323246|809214|21091614End8092145149392113232468092142109161   
111493921|1323246|809214|17968914End8092142149392113232468092141796891   
121493921|1323246|217786|11419484Intermediate217786 149392113232462177861141948   
131493921|1323246|217786|1141948|591545End1141948714939211323246217786114194859154  
141493921|1323246|217786|1141948|17968915End114194851493921132324621778611419481796891  
151493921|1323246|217786|1141948|12789255Intermediate1141948 1493921132324621778611419481278925  
161493921|1323246|217786|1141948|7150315Intermediate1141948 149392113232462177861141948715031  
171493921|1323246|217786|1141948|715031|2897746End7150319149392113232462177861141948715031289774 
181493921|1323246|217786|1141948|715031|591546End715031414939211323246217786114194871503159154 
191493921|1323246|217786|1141948|715031|17701146End71503181493921132324621778611419487150311770114 
201493921|1323246|217786|1141948|715031|21091616End71503151493921132324621778611419487150312109161 
211493921|1323246|217786|1141948|715031|7360756Intermediate715031 149392113232462177861141948715031736075 
221493921|1323246|217786|1141948|715031|736075|17968917End73607571493921132324621778611419487150317360751796891
231493921|1323246|217786|1141948|715031|736075|591547End736075514939211323246217786114194871503173607559154
241493921|1323246|217786|1141948|715031|736075|7880697End7360757149392113232462177861141948715031736075788069
251493921|1323246|217786|1141948|715031|736075|21091617End73607521493921132324621778611419487150317360752109161
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Ale Try this:

Column = 
    VAR __Path = [Path]
    VAR __Count = COUNTROWS(FILTER('Table', CONTAINSSTRING([Path], __Path)))
    VAR __Result = IF([IntermediateOrEnd] = "Intermediate" && __Count = 1, "Error", BLANK())
RETURN
    __Result

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@Ale Try this:

Column = 
    VAR __Path = [Path]
    VAR __Count = COUNTROWS(FILTER('Table', CONTAINSSTRING([Path], __Path)))
    VAR __Result = IF([IntermediateOrEnd] = "Intermediate" && __Count = 1, "Error", BLANK())
RETURN
    __Result

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you, @Greg_Deckler !

 

It worked like a charm for a small dataset. The issue right now is because I have 640k rows and the function can't be performed. It gets stuck in "Working on it".

 

Would you recommend any other approach? Is there any alternative for the CONTAINSSTRING() that could have a better performance?

@Ale Let me see what I can do. Might be better to do it in Power Query. Is that an option for you?


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@Ale This measure was designed for something similar:

Childless - Microsoft Fabric Community


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler , thanks for the reply. I'm trying to get that done with the measure you mentioned but that's not working properly. It just returns Path values. Also, I don't have any date in my Dataset.

@Greg_Deckler ,  that report is such a crime 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.