cancel
Showing results 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

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.

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 ID Path Tree Depth IntermediateOrEnd Parent Value Level1 Level2 Level3 Level4 Level5 Level6 Level7 1 1493921|1323246 2 Intermediate 1493921 1493921 1323246 2 1493921|1323246|2109161 3 End 1323246 5 1493921 1323246 2109161 3 1493921|1323246|59154 3 End 1323246 7 1493921 1323246 59154 4 1493921|1323246|1571556 3 End 1323246 2 1493921 1323246 1571556 5 1493921|1323246|809214 3 Intermediate 1323246 1493921 1323246 809214 6 1493921|1323246|217786 3 Intermediate 1323246 1493921 1323246 217786 7 1493921|1323246|809214|1571556 4 End 809214 7 1493921 1323246 809214 1571556 8 1493921|1323246|217786|59154 4 End 217786 9 1493921 1323246 217786 59154 9 1493921|1323246|809214|59154 4 End 809214 4 1493921 1323246 809214 59154 10 1493921|1323246|809214|2109161 4 End 809214 5 1493921 1323246 809214 2109161 11 1493921|1323246|809214|1796891 4 End 809214 2 1493921 1323246 809214 1796891 12 1493921|1323246|217786|1141948 4 Intermediate 217786 1493921 1323246 217786 1141948 13 1493921|1323246|217786|1141948|59154 5 End 1141948 7 1493921 1323246 217786 1141948 59154 14 1493921|1323246|217786|1141948|1796891 5 End 1141948 5 1493921 1323246 217786 1141948 1796891 15 1493921|1323246|217786|1141948|1278925 5 Intermediate 1141948 1493921 1323246 217786 1141948 1278925 16 1493921|1323246|217786|1141948|715031 5 Intermediate 1141948 1493921 1323246 217786 1141948 715031 17 1493921|1323246|217786|1141948|715031|289774 6 End 715031 9 1493921 1323246 217786 1141948 715031 289774 18 1493921|1323246|217786|1141948|715031|59154 6 End 715031 4 1493921 1323246 217786 1141948 715031 59154 19 1493921|1323246|217786|1141948|715031|1770114 6 End 715031 8 1493921 1323246 217786 1141948 715031 1770114 20 1493921|1323246|217786|1141948|715031|2109161 6 End 715031 5 1493921 1323246 217786 1141948 715031 2109161 21 1493921|1323246|217786|1141948|715031|736075 6 Intermediate 715031 1493921 1323246 217786 1141948 715031 736075 22 1493921|1323246|217786|1141948|715031|736075|1796891 7 End 736075 7 1493921 1323246 217786 1141948 715031 736075 1796891 23 1493921|1323246|217786|1141948|715031|736075|59154 7 End 736075 5 1493921 1323246 217786 1141948 715031 736075 59154 24 1493921|1323246|217786|1141948|715031|736075|788069 7 End 736075 7 1493921 1323246 217786 1141948 715031 736075 788069 25 1493921|1323246|217786|1141948|715031|736075|2109161 7 End 736075 2 1493921 1323246 217786 1141948 715031 736075 2109161
1 ACCEPTED SOLUTION
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver II

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?

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

@Ale This measure was designed for something similar:

Childless - Microsoft Fabric Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver II

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.

Super User

@Greg_Deckler ,  that report is such a crime 🙂

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors