Limit users results based on e-mail address to matching data in table

[BenGWeeks]

Hi there,

I have a column that has users e-mail addresses. I have a calculated column as follows to extract the users domain name:

Organisation = MID(DevOpsAssessmentResponses[Email],FIND("@",DevOpsAssessmentResponses[Email])+1,LEN(DevOpsAssessmentResponses[Email])-FIND("@",DevOpsAssessmentResponses[Email]))

When a user logs in to view a report, can I detect their email address (and hence domain name part) and limit the results to data with a matching domain name so they can't see other organisations results?

Many thanks.

Ben

[v-rzhou-msft · Accepted solution]

Hi @BenGWeeks 

In addition to timg‘s reply,if you want to restrict the user by their email address to see the data in your report , you can try dynamic RLS in Power BI.

My Sample:

You need to build a user table as below.

Relate two tables by Company ID columns.

Manage Roles in Modeling and add table filter expression.

 [Company ID]=
CALCULATE (
    MAX ( Data[Company ID] ),
    FILTER ( User, User[User] = USERPRINCIPALNAME() )
) 

If I am User1, result is as below.

User1 can only see values belong to him.

Then you can publish your report on Service and add roles in Security in Dataset.

For more details you may refer to this blog: Dynamic Row Level Security with Power BI Made Simple

Fpr more details about how to add role in service: Validating the role within the Power BI service

Best Regards,

Rico Zhou

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

[timg]

Hi Ben,

Yes this would be a good case to apply RLS. You can find some more information here: Row-level security (RLS) with Power BI - Power BI | Microsoft Docs.

You can use the USERNAME() and USERPRINCIPALNAME() to add RLS roles based on individual users.

Hope That helps!

Regards,

Tim

[amitchandak]

@BenGWeeks , Try like

Organisation = right(DevOpsAssessmentResponses[Email],LEN(DevOpsAssessmentResponses[Email])-FIND("@",DevOpsAssessmentResponses[Email]))