Forum Discussion
How do i use USERNAME() dax function in my store procedure as a parameter in power bi query ?
Microsoft Employee
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.
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.
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 ='[email protected]'
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
I do have same requirement. Please let me know if anybody has a solution or workaround for this
I do have same requirement. Please let me know if anybody has a solution or workaround for this
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.
