Determining if a parent in a hierarchy is disabled, for multiple test cases

[alexdi]

I have a security matrix in this format:

ID	L1	L2	L3	L4	L5	L6	L7	group1	group2
1	Menu1							Y	N
2	Menu1	Sub1						N	Y
3	Menu1	Sub2						Y
4	Menu2							Y	Y
5	Menu2	Sub1						N	N
6	Menu2	Sub1	SubSub1					Y	Y
7	Menu2	Sub2							Y
8	Menu2	Sub2	SubSub1					Y	N
9	Menu2	Sub2	SubSub1	SubSubSub1				Y	Y

L1 to L7 are string values that correspond to clickpaths. If a parent is disabled, the children are implictly disabled.

Groups associate with clickpaths. While column names from ID to L7 will be consistent, the group names will vary, and there many be up to 50.  

I'm trying to create a column that aggregates groups that, for each row, have an enabled child with a disabled parent. The user implication is you're trying to enable a clickpath, but you need to go enable the parents in the hiearchy for the child to be visible.

ID	Parent Disabled
1
2	group2
3
4
5
6	group1, group2
7
8
9	group2

I have a calculated column to do this, but it has 50 helper columns that require a lot of INDIRECTS because the groups are dynamic, and it's making the sheet unusuably slow. Can it be done in DAX? 

Some notes:

1500 rows
Up to 50 groups
Unique IDs, sequential
Unique group names, dynamic number of groups
Name/group associations can be "Y", "N", or null

This is a follow-on to the 'Table matrix grouping and transform' post here (thanks ImkeF!), and hopefully the last formula I'll need help converting:

https://community.powerbi.com/t5/Power-Query/Table-matrix-grouping-and-transform/m-p/1048155

[alexdi]

@ImkeF, thanks so much! That works perfectly, so much better that the formulas I initally had. Also, I appreciate your website-- lots of great content.

[Greg_Deckler]

Not sure about Power Query, in DAX you would use the PATH family of functions. Perhaps this is a Transitive Closure issue sort of in reverse. See this article because it refers to a Power Query article about transitive closure. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/m-p/783828#M388

But @ImkeF is likely your best best, or @edhans 

[ImkeF]

Sorry, but I don't understand the request.

Please take one or 2 examples that show the logic.

[alexdi]

@ImkeF, absolutely, I'll go through the example in the post.

ID	L1	L2	L3	L4	L5	L6	L7	group1	group2
1	Menu1							Y	N
2	Menu1	Sub1						N	Y
3	Menu1	Sub2						Y
4	Menu2							Y	Y
5	Menu2	Sub1						N	N
6	Menu2	Sub1	SubSub1					Y	Y
7	Menu2	Sub2							Y
8	Menu2	Sub2	SubSub1					Y	N
9	Menu2	Sub2	SubSub1	SubSubSub1				Y	Y

ID	Parent Disabled
1
2	group2
3
4
5
6	group1, group2
7
8
9	group2

ID1: The permissions correspond to a top-level parent, so there's no parent at a higher level that could be affecting this row for either group. 

ID2: Child of ID1. Group1 is disabled, so that one isn't relevant. For Group2, we're attempting to enable this child path, but the parent in ID1 is disabled, so the path won't be accessible; we report this group

ID3: Child of ID1. Group1 is enabled, but since ID1 is enabled for this group, no problems. Group2 has a blank, so it's ignored.

ID4: New top-level parent enabled for both groups. No conflicts possible.

ID5: Child of ID4, disabled for both groups. No conflicts possible.

ID6: Child of ID4 and ID5, enabled for both groups. If ID4 or ID5 are disabled for a group, then the group should be reported. Here, ID5 is disabled for both groups, so we report both groups. 

ID7: Child of ID4 enabled for Group2; no conflict since ID4 was enabled for Group2. Group1 is blank, so no action.

ID8: Child of ID4 and ID7. Enabled for Group1; no conflict since ID4 is enabled and ID7 wasn't explicitly disabled. Disabled for Group2; no conflict. 

ID9: Child of ID4, ID7, and ID8. Enabled for Group1; no conflict since ID4, ID7, and ID8 are all either enabled or blank for this group. Enabled for Group2; ID4 and ID7 are enabled, but ID8 is disabled, so we report this group.

Appreciate the link @Greg_Deckler, reading through it now. 

[ImkeF]

Hi @alexdi ,

please check this out:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzSsF0ZgoEoj9lGJ1opWMkNQFlyZhUe4HVg9Sa4yq1gi7uWClJlClWNRAlUGMNEVSh9t6iFPNMNUCKSy6EMabo2rB5RqQUgtMpbhNhzjIEp8WCAtdP9RpsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, L1 = _t, L2 = _t, L3 = _t, L4 = _t, L5 = _t, L6 = _t, L7 = _t, group1 = _t, group2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"L1", type text}, {"L2", type text}, {"L3", type text}, {"L4", type text}, {"L5", type text}, {"L6", type text}, {"L7", type text}, {"group1", type text}, {"group2", type text}}),
    GetLevels = Table.AddColumn(#"Changed Type", "Levels", each Record.SelectFields(_, List.Transform({"1".."7"}, (x) => "L" & x))),
    GetLevelValues = Table.AddColumn(GetLevels, "LevelValues", each Record.FieldValues([Levels])),
    SelectNonEmpties = Table.AddColumn(GetLevelValues, "SelectNonEmpties", each List.Select([LevelValues], (x)=> x <> "" and x <> null)),
    LastNonEmptyLevel = Table.AddColumn(SelectNonEmpties, "LastElement", each List.Last([SelectNonEmpties])),
    #"Removed Columns" = Table.RemoveColumns(LastNonEmptyLevel,{"L1", "L2", "L3", "L4", "L5", "L6", "L7", "Levels", "LevelValues", "SelectNonEmpties"}),
    ChildValues = Table.UnpivotOtherColumns(#"Removed Columns", {"LastElement", "ID"}, "Attribute", "Value"),
    RelevantChildValues = Table.SelectRows(ChildValues, each ([Value] = "N")),
    Custom1 = #"Changed Type",
    #"Unpivoted Only Selected Columns" = Table.Unpivot(Custom1, {"L1", "L2", "L3", "L4", "L5", "L6", "L7"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"ID", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value.1] <> "")),
    MergeWithCriticalElements = Table.NestedJoin(#"Filtered Rows1", {"Value", "Attribute.1"}, #"RelevantChildValues", {"LastElement", "Attribute"}, "Filtered Rows1", JoinKind.LeftOuter),
    #"Expanded Filtered Rows1" = Table.ExpandTableColumn(MergeWithCriticalElements, "Filtered Rows1", {"Attribute", "Value"}, {"Attribute.2", "Value.2"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Filtered Rows1", each ([Value.2] = "N") and ([Value.1] = "Y")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"ID", "Attribute.1"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"ID"}, {{"All Rows", each _, type table [ID=number, Attribute.1=text]}}),
    #"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([All Rows][Attribute.1], ", ")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom4",{"All Rows"})
in
    #"Removed Columns1"

[alexdi]

@ImkeF, that's really close! Thank you for the effort, just setting it up in place of my existing formulas radically improved the performance of the whole workbook. 

In testing, though, I found an area where it's calling out click paths that are actually OK. Here's a sample: 

https://easyupload.io/0l9ldh

I disabled "Menu 2 | Sub2 | Subsub1", which correctly shows the enabled child as a problem. However, it's also noting "Menu2 | Sub 1 | Subsub 1", which would not be affected.

[ImkeF]

Hi @alexdi ,

no problem, this should work then:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzSsF0ZgoEoxjdaKVjJDUBZcmYVGOUGuMqtYIn1oTqFosilDUmSKpw26/H1ytGaZaIIVFF8J4c1QteJ1sgamWkPGW+LRAWOj6oZpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, L1 = _t, L2 = _t, L3 = _t, L4 = _t, L5 = _t, L6 = _t, L7 = _t, group1 = _t, group2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"L1", type text}, {"L2", type text}, {"L3", type text}, {"L4", type text}, {"L5", type text}, {"L6", type text}, {"L7", type text}, {"group1", type text}, {"group2", type text}}),
    GetLevels = Table.AddColumn(#"Changed Type", "Levels", each Record.SelectFields(_, List.Transform({"1".."7"}, (x) => "L" & x))),
    GetLevelValues = Table.AddColumn(GetLevels, "LevelValues", each Record.FieldValues([Levels])),
    SelectNonEmpties = Table.AddColumn(GetLevelValues, "SelectNonEmpties", each List.Select([LevelValues], (x)=> x <> "" and x <> null)),
    #"Added Custom1" = Table.AddColumn(SelectNonEmpties, "FullKey", each Text.Combine([SelectNonEmpties], ", ")),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Parents", each Text.Combine(List.RemoveLastN([SelectNonEmpties],1),", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"L1", "L2", "L3", "L4", "L5", "L6", "L7", "Levels", "LevelValues", "SelectNonEmpties"}),
    UnpivotedGroups = Table.UnpivotOtherColumns(#"Removed Columns", { "Parents", "ID", "FullKey"}, "Attribute", "Value"),
    RelevantChildValues = Table.SelectRows(UnpivotedGroups, each ([Value] = "N")),
    #"Merged Queries" = Table.NestedJoin(UnpivotedGroups, {"Parents", "Attribute"}, RelevantChildValues, {"FullKey", "Attribute"}, "RelevantChildValues", JoinKind.LeftOuter),
    #"Expanded RelevantChildValues" = Table.ExpandTableColumn(#"Merged Queries", "RelevantChildValues", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded RelevantChildValues", each ([Attribute.1] <> null) and ([Value] = "Y")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ID", "Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"ID"}, {{"AllRows", each _, type table [ID=number, Attribute=text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([AllRows][Attribute], ", "))
in
    #"Added Custom2"

[alexdi]

@ImkeF, thank you, this version is also very close. When I posted my sample sheet though, I didn't provide enough detail and may have misled you. The first version did catch all the children of a parent. This one only triggers on the immediate parent, so an enabled child two or more levels down isn't caught:

• N
  • Y > correctly returned
    • Y > not returned 
      • Y > not returned

Here's another sample with both queries: 

https://easyupload.io/zu56ce

Can I persuade you to try once more?

[ImkeF · Accepted solution]

Hi @alexdi ,

here you go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzSsF0ZgoEoxjdaKVjJDUBZcmYVGOUGuMqtYIn1oTqFosioDID67OFEkdIfvNMNUCKSy6EFrMUbXgcLIfWK0FplpCxlvi0wJhoeuHao4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, L1 = _t, L2 = _t, L3 = _t, L4 = _t, L5 = _t, L6 = _t, L7 = _t, groupA = _t, groupB = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"groupA", "group1"}, {"groupB", "group2"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", Int64.Type}, {"L1", type text}, {"L2", type text}, {"L3", type text}, {"L4", type text}, {"L5", type text}, {"L6", type text}, {"L7", type text}, {"group1", type text}, {"group2", type text}}),
    GetLevels = Table.AddColumn(#"Changed Type", "Levels", each Record.SelectFields(_, List.Transform({"1".."7"}, (x) => "L" & x))),
    GetLevelValues = Table.AddColumn(GetLevels, "LevelValues", each Record.FieldValues([Levels])),
    SelectNonEmpties = Table.AddColumn(GetLevelValues, "SelectNonEmpties", each List.Select([LevelValues], (x)=> x <> "" and x <> null)),
    #"Added Custom3" = Table.AddColumn(SelectNonEmpties, "Levels.1", each {1..List.Count([SelectNonEmpties])}),
    #"Expanded Levels.1" = Table.ExpandListColumn(#"Added Custom3", "Levels.1"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Levels.1", "AllParents", each List.Range([SelectNonEmpties], 0,[Levels.1])),
    AddFullKey = Table.AddColumn(#"Added Custom1", "FullKey", each Text.Combine([SelectNonEmpties], ", ")),
    AddParents = Table.AddColumn(AddFullKey, "Parents", each Text.Combine([AllParents],", ")),
    #"Removed Columns" = Table.RemoveColumns(AddParents,{"L1", "L2", "L3", "L4", "L5", "L6", "L7", "Levels", "LevelValues", "SelectNonEmpties"}),
    UnpivotedGroups = Table.UnpivotOtherColumns(#"Removed Columns", { "Parents", "ID", "FullKey"}, "Attribute", "Value"),
    RelevantChildValues = Table.SelectRows(UnpivotedGroups, each ([Value] = "N")),
    #"Merged Queries" = Table.NestedJoin(UnpivotedGroups, {"Parents", "Attribute"}, RelevantChildValues, {"FullKey", "Attribute"}, "RelevantChildValues", JoinKind.LeftOuter),
    #"Expanded RelevantChildValues" = Table.ExpandTableColumn(#"Merged Queries", "RelevantChildValues", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded RelevantChildValues", each ([Attribute.1] <> null) and ([Value] = "Y")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ID", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"ID"}, {{"AllRows", each _, type table [ID=number, Attribute=text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([AllRows][Attribute], ", "))
in
    #"Added Custom2"