cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dynamic Row Level Security for the live connection with SSAS Tabular Model

Hi All,

I am implementing the Dynamic Row Level Security for the power bi users, which is live connection with SSAS tabular model.

 

I gone through the below steps:

1. I have created a table with the Users.

EmployeeIDSalesTerritoryIDFirstNameLastNameUserName
11User1AXXXX\User1
28User2BXXXX\User2
39User3CXXXX\User3
47User4DXXXX\User4
511USer5EXXXX\User5

2. Crated roles in SSAS  and added users to  membership.

And below picture for reference.

img 3 modelling.PNG

3. Used below DAX query to apply Dynamic RLS

 

=DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])

RLS dynamic.PNG

4. Shared Power Bi report to the Users(Local AD users).

5. User can not able to see the content (User could see the blank visuals).

img1.PNG

6. It is working for the Static RLS.

Given the DAX : =DimSalesTerritory[SalesTerritoryKey]=2

img2.PNG

 

For more information I have On-Prem SSAS, Local AD users and live connection with the Power BI.

what would be the reason for this. If any suggestions , it would be appriciated.Thanks in Advance. 

 

Thanks,

Lakshmi

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Dynamic Row Level Security for the live connection with SSAS Tabular Model

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: Dynamic Row Level Security for the live connection with SSAS Tabular Model

USERNAME() behaves differently on Desktop and Service. Always use USERPRINCIPALNAME() and always use email addresses, not domain accounts.

Highlighted
Frequent Visitor

Re: Dynamic Row Level Security for the live connection with SSAS Tabular Model

Thanks for the reply. 

I have changed the table and DAX query as below using UPN.

UPN.PNG

And DAX :

=DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERPRINCIPALNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])

 

But the result is same, User could see blank report.

Do I should change the DAX query? any other settings to fix it?

 

Thanks,

Lakshmi

Highlighted
Super User V
Super User V

Re: Dynamic Row Level Security for the live connection with SSAS Tabular Model

In power bi create a RLS role and for the DimUserSecurity table specif the rule

 

[Username]=USERPRINCIPALNAME()

Highlighted
Frequent Visitor

Re: Dynamic Row Level Security for the live connection with SSAS Tabular Model

Hi Ibendlin,

 

I am unable to implement RLS in Power BI desktop, since this is live connection with SSAS Tabular Model.

 

 

RLS1.PNG

 

Thanks,

Lakshmi

 

Highlighted
Super User V
Super User V

Re: Dynamic Row Level Security for the live connection with SSAS Tabular Model

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors