Forum Discussion
Post PartisanHow to apply RLS and show blanks in table for specific measure?
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 |
| 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 | |
| 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) |
| 300 | 0 (or Blank) |
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.
4 Replies
DeBIeI'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.
- AmiraBedh
Super User
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.
