Dynamic RLS with UserName() and Multiple Cost Centers and Cost Centers Nodes in hierarchy

[Haleem]

My sample files are here
https://1drv.ms/f/s!AtMKO3vGEsG5gSQODCatb86kNR48

In continuation of my previous post which was resolved 
Dynamic RLS with UserName() and Multiple Cost Centers and Cost Centers Nodes in hierarchy 

Now I would like to ruplicate SAP BW Access file to Power BI. Access File is today managed in Excel and being uploaded to BW in order to control access rights of our SAP BI Cost Reports.

I would like to use the same file and upload it to Power BI in order to manage the access rights of users automatically using the USERNAME() in Power BI cost reports, to avoid maintaining parellel two access files. 

Problems:

None of the below table contains UNIQUE field!!!!!!

1. Access file contains combination of USER, Cost Center and/or Nodes

2. Data tables contains only Cost Center column not nodes

3. But we have separate file which contains Cost Centers Per node.. so how to replaces nodes in Access file (in point 1) with the cost centers 

4. how to link Access file to the Data File and then apply USERNAME function on access file in order to limit the user to see cost center he/she is having access to. 

What I ideally want: 

1. Upload all the three files to Power BI

2. Replace Nodes in Access File with relevant Cost Centers and link Access file to the Data file

3. Use USERNAME function through RLS Roles in order to manage the access rights automatically. 

Below is the overview of each of Sample tables. 

1. Data Table

2. Access File

3. Nodes and Cost Centers

[v-frfei-msft · Accepted solution]

Hi @Haleem,

We can merge Nodes and Cost centers and Access file1 tables to get the result table as you want. 

Please refer to the M code in Power query.

let
    Source = Table.NestedJoin(#"Nodes and Cost centers",{"Nodes "},#"Access file1",{"CCtr Broadcast"},"Access file1",JoinKind.FullOuter),
    #"Expanded Access file1" = Table.ExpandTableColumn(Source, "Access file1", {"User Name", "CCtr Broadcast"}, {"Access file1.User Name", "Access file1.CCtr Broadcast"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Access file1", "Custom", each if[Cost Center] = null then [Access file1.CCtr Broadcast] else [Cost Center]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Access file1.CCtr Broadcast", "Nodes ", "Cost Center"})
in
    #"Removed Columns"

For more details, please check the pbix as attached.

Regards,

Frank

[Haleem · Accepted solution]

Thanks a lot. I have implemented the solution on REAL data and It worked well Except one problem. The Merged Table is missing some USERS and showing null value for the users who do exists in the Access file, as you can see in the below screenshot. It's not the case for all users, but there are many users that they do not not appear in the Merged table.

[v-frfei-msft · Accepted solution]

Hi @Haleem,

We can merge Nodes and Cost centers and Access file1 tables to get the result table as you want. 

Please refer to the M code in Power query.

let
    Source = Table.NestedJoin(#"Nodes and Cost centers",{"Nodes "},#"Access file1",{"CCtr Broadcast"},"Access file1",JoinKind.FullOuter),
    #"Expanded Access file1" = Table.ExpandTableColumn(Source, "Access file1", {"User Name", "CCtr Broadcast"}, {"Access file1.User Name", "Access file1.CCtr Broadcast"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Access file1", "Custom", each if[Cost Center] = null then [Access file1.CCtr Broadcast] else [Cost Center]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Access file1.CCtr Broadcast", "Nodes ", "Cost Center"})
in
    #"Removed Columns"

For more details, please check the pbix as attached.

Regards,

Frank

[Haleem · Accepted solution]

Thanks a lot. I have implemented the solution on REAL data and It worked well Except one problem. The Merged Table is missing some USERS and showing null value for the users who do exists in the Access file, as you can see in the below screenshot. It's not the case for all users, but there are many users that they do not not appear in the Merged table.

[v-frfei-msft]

Hi @Haleem,

How did you merge the tables? Did you use the full outer option?

Regards,

Frank

[Haleem · Accepted solution]

Hi Frank,

It was my mistake, I didn't use "Full outer" option  while merging tables, now I did. It's working very fine now and the issues I observed in first test is resolved now. I will do further testing.

But Thanks a lot, It is a great help for me 🙂