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
Quiny_Harl
Resolver I
Resolver I

How to restrict access to only certain measures?

Hi, folks,

 

Is there a way to restrict some users' access to one or more measure/s only?

Object level security (OLS) does not work in my case because I have visuals, for example a table visual, which contains other data in addition to the measures that should be hidden. So if I use OLS, the whole table visual will be hidden from users, which includes measures and columns that all report users must be able to see. 
The workaround with the page level security is a back-up option which I would prefer to avoid because I will have to re-work my whole report which contains many pages and then it will contain even more pages.
Thanks for your time!


1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

OK, you could modify the measure to read the user with userprincipalname() then return BLANK() if they matched a criteria.  It would look something like this.

Bonus Amount =
VAR _UPN =
    USERPRINCIPALNAME ()
VAR _UserType =
    LOOKUPVALUE ( 'Users'[Role], 'Users'[Email], _UPN )
RETURN
    IF ( _UserType = "Sales", BLANK (), SUM ( 'Payroll'[Bonuses] ) )

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User
Super User

This also sounds like a good option for applying in a calculation group.  That way, if you found new measures you wanted to restrict, you could just add them to the calculation group.

https://www.sqlbi.com/calculation-groups/

 

jdbuchanan71
Super User
Super User

OK, you could modify the measure to read the user with userprincipalname() then return BLANK() if they matched a criteria.  It would look something like this.

Bonus Amount =
VAR _UPN =
    USERPRINCIPALNAME ()
VAR _UserType =
    LOOKUPVALUE ( 'Users'[Role], 'Users'[Email], _UPN )
RETURN
    IF ( _UserType = "Sales", BLANK (), SUM ( 'Payroll'[Bonuses] ) )
jdbuchanan71
Super User
Super User

@Quiny_Harl 

Would it work if you created a dummy table to hold the measures you want to restrict.  Move the measure there (measures can belong to any table) then apply OLS to that table?

Thanks for your reply. The measures that need to be restricted are part of visuals that contain other data that needs to be seen. So when I apply the OLS, those visuals will appear broken to the users with restricted access while they should be able to see them. In the best case scenario, the measures should return a blank value when a user with restricted access opens the page.

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.