How do i use USERNAME() dax function in my store procedure as a parameter in power bi query ?

[Rinkesh]

Hi All,

I have a store proceudre which has a parameter domainname. currently i am using hard coded value to get the report and its works.

Now i want to pass loggedin user email id in power bi online as a parameter. ishort SP should be looked like as below example.

Example :  Exec SPLead @domainname ='rinkesh.rxxxx@XXXXXXs.com

Can anybody help me how do i achieve this?

Thanks,

Rinkesh Rathore

[Rinkesh]

Can anyone help me over here ?

[Sunkari]

I don't think this is supported in Power BI Service.

[Rinkesh]

Is there any workaround ?

[Sunkari]

I am not sure on your approach.

But you can think of pulling all the information into Power BI and implementing RLS.

or

Creating a tabular model with RLS.

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

[Rinkesh]

Hi Sunkari,

Thanks for the suggestion.

I have been already used RLS . As per my understanding, In RLS we can use USERNAME() function which returns logged in user name/email.
My challenge is that I want to pass this returned username or email in my SP parameter.

[Eric_Zhang]

---

@Rinkesh wrote:

Hi Sunkari,

Thanks for the suggestion.

I have been already used RLS . As per my understanding, In RLS we can use USERNAME() function which returns logged in user name/email.
My challenge is that I want to pass this returned username or email in my SP parameter.

---

@Rinkesh

It is not possible and there's no workaround. As you've already used RLS, you can alter the SP to retrieve data for all domain users and apply RLS to restrict those users to see their own data.

[Rinkesh]

Hello Eric,

Thanks for the response but i dont want to restrict users to see their only own data. I want to display the records for all users who have same business unit in CRM . The username will be used only for getting the BU from systemuser table in CRM.

please find the below sql query so that requirment can be explained better.

declare @domainname varchar(50)
set @domainname ='rinkesh.rathor@xxxxxx.com'
select * from lead L
inner join systemuser S on S.id = L.ownerid
inner join businessunit B on B.id = S.businessunitid
where L.owningbusinessunit = (select businessunitid from systemuser where domainname=@domainname)

Thanks,

Rinkesh

[Dhara_joshi]

I do have same requirement. Please let me know if anybody has a solution or workaround for this

[pasupathig]

I had the same situation. But in my case we were storing the Email id and domain access in our database for each users. So i created the role in the "Manage roles" option and added the filter in the DAX query(Username()). And in the power BI Service i just given the access to all the users. whenever the user access the reports, the data will be filtered automatically.

[Dhara_joshi]

I do have same requirement. Please let me know if anybody has a solution or workaround for this