Pass username as filter or parameter in query using DirectQuery to SQL?

[OneWithQuestion]

Is there anyway to pass UserName() or userPrincipalName() as a query parameter when doing direct query?

For example, if I want to direct query on today's sales I can handle queries like getting today's date, but I also want to filter on the sales person running the direct query.

Our sales table has the sale's person's userPrincipalName as a column but I want it to filter on that by passing a value as opposed to having to return all the values for today and then do a slicer filter on the report.

[Shaun2020]

Hi guys. Actually, this is possible in theory. If I can  use RLS and have a table with the value you want to pass to SQL, linked to USERPRINCIPALNAME() or USERNAME(), whichever you want to use, then it should be possible. Create/Upload a table to PBI with the data as a created table  or via a query
Create a measure where the USERPRINCIPALNAME() matches a value in the table using LOOKUPVALUE() and assign the resulting value to a parameter in your SQL query.
I have not tried this, but I have tried some things to display a user friendly name on the currently loaded Visual/Repoort/Dashboard. 
So yes, I am quite sure it is possible.
If anyone actually gets this to work, I will gladly like to know. If I have time I will give it a try myself. The query will have to be edited manually to get it to work. It will only load data for a matching value.
If I understand what you are asking, then this may be a solution.

[v-chuncz-msft]

@OneWithQuestion,

Row-level security (RLS) with Power BI can be used to restrict data access for given users. You may have a look.

[OneWithQuestion]

No good in this case.

We want to filter by department defaults, so that if we are viewing sales by region or department the page loads with that specific region/department filtered first.

However, we don't want to prevent them from viewing other department/region data.

Basically we have SPO sites with various department landing pages and want a link back to PBI to take viewers directly to the PBI dashboard with the linking department filtered.

[MaxW]

Is there anywhere I can vote for this enhancement? I have 100 million rows in a table. I can't refresh 25GB every day just so 10 people can access 100,000 rows

[Shiva799]

Is it possible to pass userbname to stored procedure in mcode? If So how can i do that? 

Thanks

[Shiva799]

Is it possible to pass userbname to stored procedure in mcode? If So how can i do that? 

Thanks

[parry2k]

UserName is DAX function and I don;t think you can pass this as query parameter. You can filter the records on USERNAME() once records are loaded.

[OneWithQuestion]

I was hoping to find a way to pass it into SQL as a parameter, I looked at using M but I didn't see any options for passing usernames that way (rather I didn't see a username function for M).

Ideally, I would be able to do a SELECT Col1, Col2, Col3 FROM Table1 WHERE Table1.Username=username()

(from PBI of course) however, I don't see anyway to pass system variables/parameters into queries.

I wanted to filter them BEFORE they are loaded, so if my table had 1 million records, I would much rather return only the records that matched UserName() instead of the entire table.

[angelikamariae]

Hi

Did you find a solution for this problem? I have the same issue.

tks

A.

[angelikamariae]

Hi

Did you find the answer for this? I have exactly the same issue.

thanks.

A.

[parry2k]

Well as mentioned before, unfortunately I'm not aware of that you can pass username() value to power query, it is going to be filter after table is loaded.