Forum Discussion

szymon_zabiello's avatar
szymon_zabiello
Regular Visitor
5 years ago
Solved

RLS on database level with Direct Query

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?

 

Need Help
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

3 Replies

  • szymon_zabiello's avatar
    szymon_zabiello
    Regular Visitor
    5 years ago

    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's avatar
      v-yingjl
      Community Support
      5 years ago

      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.