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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexdi
Helper II
Helper II

Accounting for disabled parents in a table matrix hierarchy

This is a followup to Determining if a parent in a hierarchy is disabled, for multiple test cases. (Thanks again @ImkeF!)

 

I have a security matrix report in this format:

 

IDL1L2L3L4L5L6L7Levelgroup1group2
1Menu1      1NY
2Menu1Sub1     2NN
3Menu1Sub2     2YN
4Menu2      1YY
5Menu2Sub1     2NN
6Menu2Sub1SubSub1    3YY
7Menu2Sub2     2NY
8Menu2Sub2SubSub1    3YN
9Menu2Sub2SubSub1SubSubSub1   4YY

 

L1 to L7 are string values that correspond to clickpaths. In the system for which this matrix corresponds, if a parent is disabled, the children are implictly disabled. However, the report matrix does not account for this implicit hierarchy. It may show a child as enabled even if the parent is disabled.

 

What I'd like is to transform that matrix into this one:

 

IDL1L2L3L4L5L6L7Levelgroup1group2
1Menu1      1NY
2Menu1Sub1     2NN
3Menu1Sub2     2NN
4Menu2      1YY
5Menu2Sub1     2NN
6Menu2Sub1SubSub1    3NN
7Menu2Sub2     2NY
8Menu2Sub2SubSub1    3NN
9Menu2Sub2SubSub1SubSubSub1   4NN

 

Essentially the same, except for each group, the children of disabled parents are also shown as disabled. 

 

Some notes from earlier: 

 

1500 rows
Up to 50 groups
Unique IDs, sequential
Unique group names, dynamic number of groups
Row/group associations can be "Y" or "N" (no nulls in this example)

"Level" column shows the deepest level of each row

 

Is there an efficient way to do this? I've tried to merge the 'find parents' logic from the earlier post, but have not been successful so far. 

10 REPLIES 10
v-lid-msft
Community Support
Community Support

Hi @alexdi ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft, same result unfortunately. With 50 groups and an abbreviated list of 13 items, it loads at about 2 seconds per row on my 7700K. 

@v-lid-msft, I was able to solve this at scale with a variation of the query provided by @ImkeF in the header. Thank you for trying this though! I think your solution is very elegant for smaller datasets. 

v-lid-msft
Community Support
Community Support

Hi @alexdi ,

 

Sorry for that we cannot understand the transform logic, does the group1 and group2 already exist in the table and does there will be more that two group column? Could you please explain the logic for ID 6 to 9 further?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft, sure, let me explain better. 

 

The first matrix is the input and represents a permission matrix. The first nine columns will always exist, named as in the sample. There can be 0-50 group columns following, all with arbitrary (but unique) names. Each Y/N reports whether the deepest level on the row (e.g., for Menu2Sub2 / SubSub1, the SubSub1) would be accessible if the row were not grouped in hierarchy. 

 

The problem is, there is a hierarchy, and a child that's enabled won't be visible if the parent is disabled. I'm trying to change each Y to an N if any of the parent rows are marked N. That's what the second matrix shows.

 

ID3, group 1: this becomes N because parent ID1 is N

ID6, group 1: this becomes N because parent ID5 is N

ID8, group 1: this becomes N because parent ID7 is N

ID9, group 1: this becomes N because parent ID7 is N

 

Group 2 logic is the same. Any row can have up to six parents. 

Hi @alexdi ,

 

We can try to use the following steps to meet your requirement:

 

1. unpivot all the group coulmn:

 

2. Rename them as "GroupName" and "GroupValuue" 

 

3. add four custom columns

 

 1) Level.1

 

Text.Combine(Record.ToList(let le = [Level]
in
Record.SelectFields(_,
    List.Select(
        Record.FieldNames(_), 
        each  List.Contains(
            List.Transform({1..le},each Text.From(_)),
           Text.Replace(_,"L","")
            )
        )
    )),",")

 

 2) PreviousID

 

let currentLevel = [Level.1], le = [Level]
in
Table.Distinct(Table.SelectColumns(Table.SelectRows(#"Added Custom", each Text.StartsWith(currentLevel,[Level.1]) and le >= [Level]+1), {"ID"}))

 

 3) PreviousGroupValueTable

 

let GN = [GroupName]
in
Table.AddColumn([PreviousID],"PreviousGroupVaule",
each
let i = [ID],
temp = 
Table.Max(Table.SelectRows(#"Added Custom1", each [ID] = i and GN = [GroupName]),"GroupValue")
in if temp = null then [GroupValue] else temp[GroupValue])

 

4) NewGroupValue

 

if Table.RowCount(Table.FindText([PreviousGroupValueTable],"N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]

 

4. remove four  columns: "GroupValue", "Level.1", "PreviousID", "PreviousGroupValueTable"

 

5. pivot coulmns "GroupName" and "NewGroupValue" as "Do not aggregate"

 

4.jpg

 

All the queries are here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzSsF0Qo4MEjOD4gjlWJ1opWMkHQElybh0mgE1eQH1mSMqskIj6ZIuCYTqCZcqmFui4S7zRRJB9FuM8PUBKRwaDdGsc8cVSs+byEC0AJTE2H7IE61xKcVwsJijgnCzbEA", 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, Level = _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}, {"Level", Int64.Type}, {"group1", type text}, {"group2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "L1", "L2", "L3", "L4", "L5", "L6", "L7", "Level"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "GroupName"}, {"Value", "GroupValue"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Level.1", each Text.Combine(Record.ToList(let le = [Level]
in
Record.SelectFields(_,
    List.Select(
        Record.FieldNames(_), 
        each  List.Contains(
            List.Transform({1..le},each Text.From(_)),
           Text.Replace(_,"L","")
            )
        )
    )),",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PreviousID", each let currentLevel = [Level.1], le = [Level]
in
Table.Distinct(Table.SelectColumns(Table.SelectRows(#"Added Custom", each Text.StartsWith(currentLevel,[Level.1]) and le >= [Level]+1), {"ID"}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "PreviousGroupValueTable", each let GN = [GroupName]
in
Table.AddColumn([PreviousID],"PreviousGroupVaule",
each
let i = [ID],
temp = 
Table.Max(Table.SelectRows(#"Added Custom1", each [ID] = i and GN = [GroupName]),"GroupValue")
in if temp = null then [GroupValue] else temp[GroupValue])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "NewGroupValue", each if Table.RowCount(Table.FindText([PreviousGroupValueTable],"N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"GroupValue", "Level.1", "PreviousID", "PreviousGroupValueTable"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[GroupName]), "GroupName", "NewGroupValue")
in
    #"Pivoted Column"


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft, this an excellent solution for the same set, and the result is exactly correct. I also appreciate that you broke down the logic. Unfortunately, additional rows and columns seem to exponentially slow the query down. With, e.g., 50 rows and 10 groups, the query processes 2 rows per second on my machine. An attempt on the full set of ~1500 rows and 50 groups made no progress. Is there a way to do this more efficiently? 

Hi @alexdi ,

 

Could you please add step one by one and refresh one by one to find out until which step the query still run smoothly?


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft, the step that seems to be the issue is this one-- 

 

 #"Added Custom3" = Table.AddColumn(#"Added Custom2", "NewGroupValue", each if Table.RowCount(Table.FindText([PreviousGroupValueTable],"N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]),

 

I'm not sure why, the logic is sensible. This is pretty easy to test-- if you copy and paste a few more columns or rows, it'll get lethargic very quickly. 

Hi @alexdi ,

 

Maybe the Table.FindText function is too heavy, Please try to change this step to following:

 

if Table.RowCount(Table.SelectRows([PreviousGroupValueTable],each [PreviousGroupVaule] = "N"))>0 and [GroupValue] = "Y" then "N" else [GroupValue]

 


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors