Help required to implement security...

[pbiforum123]

For one of my security requirement. I will have following table as input and thinking of following table as output but dont think that is the optimized way of doing it. For lesser number of records it looks fine but when the volume of the record is too high this will not workout.

If you have any sample application which does the same. Please share the same...

Please let me know the optimised way of implementing this.

Input:

Output:

[NikitaDalela]

Hi @pbiforum123 ,

Not sure if i understand the question, but you can implement dynamic RLS (using USERNAME() DAX). By doing so, when a user views the report, he will be able to see his respective group only.

Thanks!

[pbiforum123]

Thanks for responding! I know how to implement security. So my question is not about that.

If you see the above post, I will receive the above table from the client to give access to certain groups. If the group name is blank, then it means that they should access to all groups. I know to get this work I need to have dataset as shown in the output table but as you know this is not the optimized way to implement this as when the volume of data increases creating one one record for each group is tedious job. 

So my question here is, is there is any better/optimised way to implement this.

Hope it is understandable now. Please let me know if you have any questions.

[Mariusz]

Hi @pbiforum123 

Please see the attached file with a Power Query Solution included.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pbiforum123]

@Mariusz Please attach the file again to take a look

[Mariusz]

Hi @pbiforum123 

No idea why you can not open the file as just checked and its working fine on my end.

Instead, please see the below M script, you can paste it into the advance editor of a blank query and investigate the steps.

Let me know if you need any assistance.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKdkjPTczM0UvOz1XSUUpUitWJVkpJTUMRTQKLVlRWoYgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [user = _t, group = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"group", "_group"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","",null,Replacer.ReplaceValue,{"_group"}),
    groups =  List.RemoveNulls( #"Replaced Value"[_group] ),   
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "group", each if [_group] = null then groups else { [_group] }),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"user", "group"}),
    #"Expanded group" = Table.ExpandListColumn(#"Removed Other Columns", "group"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded group",{{"group", type text}})
in
    #"Changed Type"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pbiforum123]

@Mariusz I am little new to Power BI. Can you please elaborate the steps so that I can take care of the same.

[pbiforum123]

@Mariusz I am unable to reproduce the steps to execute the query. Please assist me for the same...

[Mariusz · Accepted solution]

Hi @pbiforum123 

Please see follow the steps in the video.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pbiforum123]

@Mariusz Thanks a lot for this. Currently facing some network issue. I will keep you posted as soon as it is fixed.

[pbiforum123]

@Mariusz I followed your video and it worked like a magic! Thanks a lot..

But I dont want to stop there. I would like to understand step by step. So that if I come across any such requirement I can manage it myself.

I have couple of more questions,

1. Your solution works perfectly if all the groups exists in the security table. Like in your example, we have group called as "a" and "b" only. What if there is a group called "c" in the FACT table but that has not been given access to any of the users? So in your sample access to "c" will not given to so and so users. So can you please provide me one more sample if possible which can handle these sorts of scenario?

2. As I mentioned in the initial post getting the output with the desired data set will resolve this issue but what if the volume of the records is huge. This will definitely add number of records drastically. So is there is any better solution for this which is much optimized?

Sorry to add some more questions to the post. Didn't wanted to create new post for the same since it will loose the continuity. Hope you can understand...

[Mariusz]

Hi @pbiforum123 

The groups step ( list ) that we extracted from the security tables column can be replaced by an independent table with a list of all the groups, you can still refer to other objects outside of the scope of your query.

Power BI is designed to handle large volumes of records so expanding the number of rows is not an issue as long as you keep the columns in this table to the minimum.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pbiforum123]

@Mariusz I need your help in getting the groups from independent table. Rest was understandable! Please help...

"The groups step ( list ) that we extracted from the security tables column can be replaced by an independent table with a list of all the groups, you can still refer to other objects outside of the scope of your query."

I tried like below, hope this the right approach..

groups = List.RemoveNulls( List.Distinct( #"Replaced Value"[group] )),
groups1 = List.RemoveNulls( List.Distinct(group1[group1])),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "new group", each if [group] = null then groups1 else {[group]}),

[Mariusz]

Hi @pbiforum123 

You can create extra Table groups, like below

next, add a step to convert it to list.

and later you can use it in your code like below

#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "new group", each if [group] = null then groups else {[group]}),

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pbiforum123]

@Mariusz Thanks a lot! I exactly did the same as I mentioned in my previous post I think you missed it out. I got some good understanding on M Script to start with. It is all because your help and assisstance. 

groups = List.RemoveNulls( List.Distinct( #"Replaced Value"[group] )),
groups1 = List.RemoveNulls( List.Distinct(group1[group1])),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "new group", each if [group] = null then groups1 else {[group]}),

Please share some tutorials if you have any about the M Script. 

[pbiforum123]

@Mariusz Sure thanks a lot for your assisstance. I will follow as per your guidance and will keep you posted. Expect some delay in my response since I am very new to Power BI.

Few quick things,

I am not able to understand how this connects the source table. For example after wathing your video I have created the table same as you shown ("_input ( step by step )") and copy pasted your query as that required table has been created like a magic but I am not able to understand how it has connected to above table "_input ( step by step )" rest of the steps are some what understandable. I mean after creating the table i have followed these steps, New Query --> Blank Query --> Advanced Editor , after that i am not sure what to do. Please explain for better understanding... If you can share another video which explains about the starting steps that would be great. If you can write down the steps that is also fine for me.

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKdkjPTczM0UvOz1XSUUpUitWJVkpJTUMRTQKLVlRWoYgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [user = _t, group = _t]),

[pbiforum123]

@Mariusz Will check and comeback to you again.

Why dont you try sharing the file again? So that i can check one more time.

[Mariusz]

Hi @pbiforum123 

No problem, I've edited my previous post to includ the file.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[pbiforum123]

@Mariusz I am still unable to open that you have attached again. So please let me know the steps so that I can apply the query and check.

[pbiforum123]

I am unable to open the attached file. I am getting following error. Please attach it again...

[Mariusz]

Hi @pbiforum123 

Not sure if I understand the question but, just create the "output" table and add it to your model for RLS

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn