RLS on database level with Direct Query

[szymon_zabiello]

Here is the scenario.
Source: Teradata 
Connection mode in Power BI: Direct
Requirement: Identify the user logged in, by USERPRINCIPALNAME() function and pass its value to the direct query, in order to filter the data.

Ideal solution: Pass the value of measure User_Id = USERPRINCIPALNAME() to Power Query parameter, so that it would go to my WHERE clause as a parameter and filter the data.

So far, I did not find a solution. Is it even possible?

[szymon_zabiello]

Hi @lbendlin 

So, you're referring to "If you're using DirectQuery, the security roles in your data source are used. When a user opens a report Power BI sends a query to the underlying data source, which applies security rules to the data based on the user's credentials."

How does it fit into such case?

1. I've got a table UserRoles

UserId | RoleId

1234   | 1

3456.  | 2

  ...      | ...

2. Table FactData

Column 1 | ... | RoleId

Value 1.    | ... | 1

Value 2.    | ... | 2

    ....         | .... | ...

3. I've got a measure User = USERPRINCIPALNAME() that is returning the logged user

4. My Power Query is sth like

select * from FactData f

join UserRoles u on f.RoleId = u.RoleId

where UserId = @LoggedUserId

Question, how do I pass the measure User that is defined in PBI Desktop, to the query in Power Query? 

[v-yingjl · Accepted solution]

Hi @szymon_zabiello ,

Currently any measures and calculated columns could not be quoted in power query.

You can try to configure RLS directly in the data source side. Refer: Row-Level Security 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Why would you need that? The database already knows which user is running the direct query?

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Last FAQ.