Can we retrieve logged in user name in Power BI Query editor and passed as a parameter sql query

[Anonymous]

Can we retrieve logged in user name in Power BI Query editor and passed as a parameter to dynamic sql query.

We are trying to retrieve data from a table dynamically based on the logged in user in Power BI. Is there a way to accomplish the same. Any feedback would be appreciated

[Sergiy]

It might be of some interest to you:

https://querypower.com/2017/04/03/4-ways-to-get-username-in-power-query/

[Kalpavruksh · Accepted solution]

Hi Rinky,

Currently, there is no way to retrieve the logged in username. DAX function Username() can be helpful to return the username that the person is sign in using its Power BI credentials.

Kalpavruksh Technologies | Microsoft Gold Partner
Denmark | USA | India | Germany

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

[ImkeF]

Unfortunately there is no native function in Power Query that does that. 

[Nolock]

Hi @Anonymous,

as @ImkeF said, it is not possible in PowerQuery.

But you didn't describe your scenario. Maybe Row-level security will help. And theoretically, if you used a DirectQuery and DAX (USERNAME) you'd be able to send an SQL query containing the current user directly to an SQL Server.

[Anonymous]

  I want apply userprincipal function(logged in user name) in custom parametrer and then custom parameter in sql query. How can i do it. Please help me.

[hai_pbi]

Hi @Nolock , could you please give more hint on how to pass USERNAME DAX Function in a SQL Query using direct query ?
I'm aware of how to pass a parameter to a query, but I don't see any way of passing the currently pbi logged user (or any DAX / measure formula).
Any help would be greatly appreciated. Cheers

[Nolock]

Hello @hai_pbi,

if you use RLS, then PBI will send an SQL query like that. You see there is a where clause with the email address of the current user. More info: Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

SELECT 
TOP (1000001) [t0].[C3]
FROM 
(
(
select [$Table].[C1] as [C1],
    [$Table].[C2] as [C2],
    [$Table].[C3] as [C3]
from [dbo].[taxi] as [$Table]
)
)
 AS [t0]
WHERE 
([t0].[C3] = N'your@email.com')

GROUP BY [t0].[C3]

[hai_pbi]

Thanks a lot for this (very) rapid answer.
I see what you mean, if RLS is set with a filter applied on a table (that has a username column) with DirectQuery mode, the query sent to SQL server already filter the table correctly. Thanks.