RLS on two columns based on OR

[Lewisdc]

Hi all - i've been searching online for most of this week to a solution but couldn't find one similar to my scenario.

I have two tables, Cases and Users. 

In Users, I have the email address the user will login with, including their Department.

In Cases, there are thousands of rows that include two columns, Originating Team and Resolving Team.

Now, I can create security roles for each Department individually using:

'Cases'[Originating Team] = "Department Name" || 'Cases'[Resolving Team] = "Department Name"

But this would mean adding hundreds of users individually to the security roles in the service.

Instead, I've been trying to use 'User'[User Name] = USERPRINCIPALNAME(), however, with my knowledge of DAX I can only get this to filter one of the columns (Either Originating Team or Resolving Team) based on a relationship. This removes a large number of cases that are related to their department.

How can I use USERPRINCIPALNAME to filter both team columns as an OR statement?

Thanks in advance

[Anonymous]

Hi @Lewisdc ,

What about unpivoting the [Originating Team] column and the [Resolving Team] column, which makes two columns into one?

Unpivot columns (Power Query)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.