Row Level Security with multiple tables

[FelipePalomino]

Hello everyone! 🤓

I have a dimension and a fact table, and I need to apply some conditions for a especific function in RLS.

My dimension table has Code and Department (each department can have a variety of codes).

This works well, I can set the access accordingly to the departments, so each person only sees specific groups of codes (and sometimes some codes from other departments).

My problem is when I need to apply a second condition (that is not on this table).

I have a column with names in my fact table, and sometimes people needs access to a specific set of nomes. The RLS should be as follows:

See Department x and Codes 1, 2 and 3 (from the dimension table) plus any rows with the name "abc" (in the fact table), even if this person is not on one of the department or codes listed before.

Is it even possible?

Should I create another dimension table for this to work (maybe just a simple summarize). If so, what would the code look like?

Really appreciate if anyone can help! 😊

[amitchandak]

@FelipePalomino , Are they from the same table, department, and code? You can create a table Department. Code, Email, and dim key and can join if with dimension and apply RLS

If from two different tables. Have two tables and join with respective dim and add in same role

Power BI- Row Level Security: Handle ALL, UserPrincipalName: https://youtu.be/KVLEnIUo4pc

[FelipePalomino]

This is how my tables look like:

Table 1 - dDepartment (dimension)

CC Code | Department

1       | Dep 1

2       | Dep 1

3       | Dep 1

4       | Dep 2

5       | Dep 2

6       | Dep 3

Table 2 - dFunctions (Department)

Function Code | Function Area

1       | Area 1

2       | Area 1

3       | Area 1

4       | Area 2

5       | Area 2

In my fact table, I have several columns, with Function Code and CC Code being two of them.

I want to create a RLS function that limits  the access based on both dimension tables, like this:
Department 1 OR Area 1

So the person accessing can see BOTH the CC Codes of Department 1 AND Function Codes of Area 1.

They need to see both population, even if I have someone on Area 1 that belongs to Department 2.

My problem is, if I create a functions with this restriction on both tables, the person would only be able to see people the rows that fit both restrinctions, and not only one of them.

Is it clear now? Let me know if I need to develop further.

Thanks! 😉