Check whether a parent has children in Hierarchy

[Ale]

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

[Greg_Deckler · Accepted solution]

@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

[Ale]

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?

[Greg_Deckler]

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

[Greg_Deckler]

@Ale This measure was designed for something similar:

Childless - Microsoft Fabric Community

[Ale]

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.

[foodd]

@Greg_Deckler ,  that report is such a crime 🙂