Forum Discussion

Rinkesh's avatar
Rinkesh
Regular Visitor
9 years ago

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

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...
Need Help
Rinkesh's avatar
Rinkesh
Regular Visitor
9 years ago

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

 

 

Dhara_joshi's avatar
Dhara_joshi
New Member
8 years ago

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

  • pasupathig's avatar
    pasupathig
    New Member
    8 years ago

    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.