Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Row-Level Security with Direct Query

Hi 

I  am working in the Project  which  will   go to  PowerBI  embedded finally .We did not  upload to AZURE yet

At this moment  

Connection is  in power BI  desktop  with DIRECT  QUERY  to 2016 SSAS  Tubular .

I am   working and testing  in  Row-leverl  security  portion  because  we only allow customer  look at their own  purchase history.

I try to follow 

https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-rls/

But the  Manage Roles is  grey out in Direct Query mode. 

All the customer  ID  is  outside domain.

What should I do?

Thank-you 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@Anonymous

 

In Power BI, when connecting SSAS tabular data source in DIRECT QUERY mode, it will directly take the role security defined within Tabular model. We can't "Manage Roles" for Tabular source in Power BI Desktop. To edit roles and corresponding row filters, you have to do it in either SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Please refer to tutorial below:

 

Tutorial: Dynamic row level security with Analysis services tabular model

 

Regards,

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi,

 

I m using Impala for Data Source in Direct Query Mode.

But seeing an error message "Function LOOKUPVALUE is not allowed as part of row level security expression on DirectQuery models" when I try creating an expression.

 

I have enabled "Allow unrestricted measures in Direct Query mode" 

 

1. Is it possible to implement Dynamic RLS with Impala for Data Source?

2. Numbers of users in the company is large (runs into millions) and number of levels are 7 (Region, Country, Division, BU, Group, Local Group, Plant). It is not practical to maintain separate rows at the granular level (plant) for each user. We will then have around 10,000,000 rows for CEO and people with upper management.

 

I have two tables -->

1. Master with columns for each field

2. Security with email and matching fields from Master table

While it works in import mode, it fails in Direct Query mode with the error specified.

 

DAX expression I had created made use of LOOKUPVALUE as below:

 

OR(
OR(
OR(
Master[rc]=LOOKUPVALUE('Security'[rc],'Security'[email],USERPRINCIPALNAME(),'Security'[rc],Master[rc])
,
Master[region]=LOOKUPVALUE('Security'[region],'Security'[email],USERPRINCIPALNAME(),'Security'[region],Master[region])
)
,
OR(
Master[rcd]=LOOKUPVALUE('Security'[rcd],'Security'[email],USERPRINCIPALNAME(),'Security'[rcd],Master[rcd])
,
Master[rcdb]=LOOKUPVALUE('Security'[rcdb],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdb],Master[rcdb])
)
)
,
OR(
OR(
Master[rcdbp]=LOOKUPVALUE('Security'[rcdbp],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdbp],Master[rcdbp])
,
Master[rcdbpl]=LOOKUPVALUE('Security'[rcdbpl],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdbpl],Master[rcdbpl])
)
,
Master[rcdbplp]=LOOKUPVALUE('Security'[rcdbplp],'Security'[email],USERPRINCIPALNAME(),'Security'[rcdbplp],Master[rcdbplp])
)
)

 

Thanks,

Mannu

v-sihou-msft
Employee
Employee

@Anonymous

 

In Power BI, when connecting SSAS tabular data source in DIRECT QUERY mode, it will directly take the role security defined within Tabular model. We can't "Manage Roles" for Tabular source in Power BI Desktop. To edit roles and corresponding row filters, you have to do it in either SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Please refer to tutorial below:

 

Tutorial: Dynamic row level security with Analysis services tabular model

 

Regards,

Anonymous
Not applicable

Hi Simon

 

I have two question . 

First

All the  customer is not  a domin user . What can I do .

Second

On this  

https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-rls/

It must use import data mode .  It would not  able to use  direct query

we can not  refresh  the data at this point if we are usin import data model

Please confirm. It was becasue  we are planning  using  PowerBI Embedded to our appication.

 

Thank-you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.