Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ThamaraiSelvan
Regular Visitor

How to pass the logged in username from Power Bi report to azure analysis service?

Hi Everyone,

 

Greetings!

 

The task in my hand is to individualize the power bi report based on the logged in user i.e., logged in user should view only the data related to them.We are using Azure Analysis Service(AAS) Database as a datasource for the report.

 

We can use Row Level Security(RLS) in power bi to do the above task but we are connecting to the azure analysis service through "connect live" method which disables the RLS option.

 

On researching, I got a solution to do the RLS at AAS level. So I created two roles in my model (Admin and Login) which will restrict the user in that role group based on a DAX expression.

 

I have a Customer table which has Name,Email and Username of the users. Also there are two more tables, Product and Sales with

 

For Admin role. the members doesn't have any restriction. They can see all the data from the customer table.

 

For Login role, I put a DAX expression on the customer table as ='Customer'[Email] = USERPRINCIPALNAME() to retrieve only the rows based on the user principal name. 

 

With that in place, I used "Analyse in Excel" option to test the roles. When I use Admin role I can see all the rows in the Customer table and when I use Login role, it has returned only one row which has my email. Worked perfectly. So, I deployed my model to my Azure Analysis Service.

 

As a next step, I connected my model as a data input for a sample power bi report and created a pie chart with tha data I have and published it to my workspace in the power bi service. When I view the report on the power bi service, as a logged in user, I need to see only my data but I can actually see all users data.

 

On debugging, I thought since I put myself in both Admin and Login role group it is not working. So I removed myself from Admin role in my model, redeployed my model and checked again in my report. Still, I got the same result. Then I thought since I created the report, it is not working for me so I shared my report to other users in my customer table. But the result is same, they can also see all users data.

 

So I searched a solution for this and I found many references (both microsoft and others) but the model used in those references are from on-premises SSAS where they are using a gateway to connect between model and report. And it the solution they mentioned to map Usernames correctly. In the gateway configuration in power bi, we can map usernames with two options one going with "EffectiveUserName" and second with "CustomData".  Since, the references state that this solution works on Azure Analysis Servces too, I thought ths will work. But, we are not using any gateway for AAS since all are in cloud.

 

Now I got struck on following items

1) how I can send the logged in username to the AAS model?

2) how I check what format the "Username" is being sent to AAS model when the user views the report?

 

Also, point me in right direction if I'm doing anything wrong or missing something.

 

Thanks in advance!

2 REPLIES 2
GilbertQ
Super User
Super User

Hi @ThamaraiSelvan 

 

You could create a measure in your model which captures the logged in user such as

 

UPN = USERPRINCIPALNAME()

 

And then put the measure into the report in a table or visual.


Make sure that you have allowed AAS to connect to Azure services. Once that is done make sure you also click on the data source credentials and authenticate with an account that has got Admin access to AAS.

 

After that you should be good to go.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi GilbertQ,

 

Thank you for your response. 

 

I'm not sure I have understood you correctly. I have created a measure in my model which is going to hold the USERPRINCIPALNAME() and published it. I can see it in the Power BI report too when I connect my model as a datasource. My question here is how I can use it to filter the data? (this is the task in my hand)

 

Thanks!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors