Hide blank rows in output of dynamic row level security

[nimz]

I have the following 3 tables in my model for an application access request report.  I have implemented dynamic row level security on the 'Request' table because only the owner of application should view the requests for that application. 

1.'Application' Table

Application_Name

App1

App2

2.'Request' Table

Request_ID	Application_Name
123	App1
345	App2
456

3.'Application Owner' Table

Owner_Name	Application_Name
abc\user1	App1
abc\user2	App2

I've used the following DAX to do this:

'Request'[Application_Name]=CALCULATE(VALUES('Application Owner'[Application_Name]),
SUMMARIZE('Application Owner','Application Owner'[Owner_Name]),
'Application Owner'[Owner_Name]=USERNAME())

On testing the security as 'abc\user1', owner of the application, App1,

I expect the user to be able to see only the following data from the 'Request' Table:

Request_ID	Application_Name
123	App1

However, I am seeing the following:

Request_ID	Application_Name
123	App1
456

I am not sure why the blank entries are showing up. Is there a way to hide the rows that don't have an application name in the 'Request' table?

[Anonymous]

add filter condition to ur expression  not(isbalnk(applicationname)

[v-juanli-msft]

Hi @nimz

As tested, i can get the proper rows for a spefic user.

1. manage the relationship among three tables

table 'Application' to 'Request' based on [Application_Name]

table 'Request'  to 'Application Owner' based on [Application_Name]

2.create a new role for table 'Application Owner' 

Then publish the report to power bi service and add users's account to this dataset by "dataset"->security->add members.

when test with a user's account, i would only see the data the user is able to see.

plase see reference how to create Roles and set dynamic row level security.

https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

http://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi

Best Regards

Maggie

[MattAllington]

It depends on the relationships between the tables and  the direction of filter propagation. Check there is a path from the owner table to the request table, and ensure that the setting for RLS is turned on within the relationship.