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

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

LDevi86 — Wed, 15 Jul 2020 18:21:58 GMT

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.

EmployeeID	SalesTerritoryID	FirstName	LastName	UserName
1	1	User1	A	XXXX\User1
2	8	User2	B	XXXX\User2
3	9	User3	C	XXXX\User3
4	7	User4	D	XXXX\User4
5	11	USer5	E	XXXX\User5

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

And below picture for reference.

3. Used below DAX query to apply Dynamic RLS

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

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).

6. It is working for the Static RLS.

Given the DAX : =DimSalesTerritory[SalesTerritoryKey]=2

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

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

lbendlin — Thu, 16 Jul 2020 00:47:26 GMT

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

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

LDevi86 — Thu, 16 Jul 2020 05:36:01 GMT

Thanks for the reply.

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

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

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

lbendlin — Thu, 16 Jul 2020 11:02:09 GMT

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

[Username]=USERPRINCIPALNAME()

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

LDevi86 — Thu, 16 Jul 2020 11:38:23 GMT

Hi Ibendlin,

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

Thanks,

Lakshmi

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

lbendlin — Thu, 16 Jul 2020 12:10:31 GMT

In that case see if the below can help.

https://radacad.com/row-level-security-with-ssas-tabular-live-connection-in-power-bi

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

sultanan3 — Fri, 11 Nov 2022 05:31:39 GMT

Hi Lakshmi, I also have same porblem, Row level security is not working. In my case viewers can see all countries, I wonder if you get the solution so I can implement that way. Thank you