Row Level Security based on EmailID

[Anonymous]

Hello,

I am working on a dashboard where we have 4 potential users: Business Head, Sales Rep, District Manager and Region Manager.

Each Account has 4 of these emails associated and I have an Account ID that matches all 4.

I'm trying to create a security model so that when their email ID is used to access the report, the model will match their email to the Account ID and show the information for all 4.

There is no hierarchy between them so I cannot use the 'Path' DAX to try to establish a connection that I'm reading online.

Essentially the 4 users are a team that manages the account so I want them to see the data for that account only.

There are thousands of these emails so I can't add roles manually, and need some DAX suggestion to use that.

Just to explain: One table has the emails and the ID associated to it. The other table has the main account info with the Account ID, and I have built a relationship between the 2 and used Account ID as a connection.

Thank you!

[Payeras_BI]

Hi @Anonymous,

I'll better share my testing with you.

Table AA:

Table XX:

Because I am working in PBI desktop I'll use my local machine's username instead of my email address just for testing purposes.

No relationship needed:

Creating the Role:

Table AA is filtered accordingly:

I hope this helps.

[Payeras_BI]

Hi again @Anonymous ,

If I understood correctly this would be the Table filter DAX expression to be applied to your table AA:

[Account ID] = CALCULATE ( MIN ( XX[Account ID] ), XX[email] = USERPRINCIPALNAME () )

[Payeras_BI]

Hi @Anonymous,

Something like this should work as a Table filter DAX Expression for your main table. 

[ID] = CALCULATE ( MIN ( Emails[ID] ), USERPRINCIPALNAME () = Emails[Email] )

[Anonymous]

Hello, thank you for this response. The DAX expression that you gave me, I tried using that for table filter DAX expression however I get this error: "A single value for column 'email' in Table 'XX" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result"

Would this measure allow any user that has the email to match to the Account ID and only show that results? 

Not sure why it's not working.

More context: Table 1 (AA) has the Account ID and other account details.

Table 2 (XX) has column 'email' and 'Account ID'. so the DAX I wrote, took it from Table XX, was that right?

[Payeras_BI]

Hi @Anonymous,

Please type in the DAX expression used with your tables and column references.

[Anonymous]

CALCULATE ( MIN ( 'XX'[ID] ), USERPRINCIPALNAME () = XX[email] )

This is what I typed in the table filter DAX expression when I got the error.