How to apply RLS and show blanks in table for specific measure?

[DeBIe]

Hi all,

I would like to return blank values or the value 0, based on the RLS role. Because some users are not allowed to see the processing time. I will explain the tables that are in my model and their relations. As well I will show the table how it looks like now and how I would like it to be. Any help is much appreciated!

I have 3 tables.

- Lookup Company

- Fact [Documents]

- Fact [ProcessingTime]

Relations:

(Company) 1 - * (Documents)

(Documents) 1 - 1 (ProcessingTime)

Now

Company	Total Documents	DocumentsProcessingTime
Apple	100	2d
Google	300	1d

What I tried to do is add the column [HasAccess]=1 to the (ProcessingTime) table. I created two RLS roles. One with [HasAccess]=1 and the other with [HasAccess]=0. The problem when applying the RLS role with [HasAccess]=0, is that my (Documents) table will be filtered and will show no results anymore in the table. Then this will happen:

Company	Total Documents	DocumentsProcessingTime
Apple		Blank
Google		Blank

The desired end result that i'm looking for is this (when RLS is applied for user that are not allowed to see the processing time)

Company	Total Documents	DocumentsProcessingTime
Apple	100	0 (or Blank)
Google	300	0 (or Blank)

[DeBIe · Accepted solution]

I've solved my issue by implementing Object Level Security and field parameters. This will remove the entire column which should not be visible for some users, but the table will not be broken.

[DeBIe]

@AmiraBedh  I've uploaded an example pbix. file. If you select the role Viewer, then you will see what I mean. My desired result is that I will see blank values for the column ProcessingTime when I select the viewer role. 

https://we.tl/t-nmImBERdCM 

Thanks again

[AmiraBedh]

Try to create a measure : 
Processed Time = IF(HASONEVALUE('ProcessingTime'[HasAccess]), 'ProcessingTime'[DocumentsProcessingTime], BLANK())

It will check if there is only one value for the 'ProcessingTime'[HasAccess] column which is determined by the RLS role, and if so, it returns the 'ProcessingTime'[DocumentsProcessingTime] value. Otherwise, it returns blank.

Then use it in your visual instead of directly referencing the 'ProcessingTime'[DocumentsProcessingTime] column.

And try to update the "Processed Time" measure to display 0 instead of blank:

Processed Time = IF(HASONEVALUE('ProcessingTime'[HasAccess]), 'ProcessingTime'[DocumentsProcessingTime], 0)

[DeBIe]

Hi @AmiraBedh ,

Thank you for your suggested solution. I have a question before I can try your formula.

My [Documents] Table contains more documents then in the related table [ProcessingTime] table. When I apply the RLS-Role (HasAccess=1), then the total count of my [Documents] table will be limited/filtered to the total that are found in the [ProcessingTime] table. I need to be able to find a solution for this first. Am I using this correctly? Please help.

Thanks so much already for your time.