Analysis Service Tabular Model 2019 security for Power BI report

[kaushikmakadia]

Hi there,

I have special requirement on Analysis Service 2019 Tabular model security. Can someone help with idea on how to do orDAX or anything else solution.

In SSAS Tabular model has below 2 x tables:

TICKET

TicketID   SensitiveFlag   Descirption
T567           1                   Posting in the process 
T965           1                   Additional questions to clear
T899           0                   Create the thread process
T876          0                    Feedback specific

PERSON

PersonUserID    PersonRole
PUID01                 Head
PUID02                 Head
PUID03                 Staff
PUID04                 Staff

When person user login to Analysis service tabular model 2019 to browse the data:

CASE WHEN PersonRole= Staff AND SensitiveFlag = 1 THEN Description to replace with NULL
CASE WHEN PersonRole= Head AND SensitiveFlag = 1 THEN Description then show Description column value
CASE WHEN PersonRole= Staff AND SensitiveFlag = 0 THEN Description then show Description column value
CASE WHEN PersonRole= Head AND SensitiveFlag = 0 THEN Description then show Description column value

e.g. When PUID01 login to SSAS tabular model and drag-n-drop Description column then that user must see value from this column regardless of SensitiveFlag value.
e.g. When PUID03 login to SSAS tabular model and drag-n-drop Description column then that user must see value from this column where SensitiveFlag is zero i.e. T899 BUT see NULL value for SensitiveFlag is 1 i.e. T567

Yes Person.PersonUserID is same value as user login id to match from Power BI report.

Any idea please.

Many Thanks.

[v-angzheng-msft]

Hi, @kaushikmakadia 

It sounds like an RLS solution. Row level security is a Power Bi feature that allows you to create rules for users. Those rules (DAX code) will filter the current model.

You can read more in microsoft doc: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

That will show how to implement it.

Here you have a second link in case you feel that the first documentation won't work. There are ways to make it dynamic but it might be a little more complex: https://blog.ladataweb.com.ar/post/652895563673649152/powerbi-seguridad-de-filas-rls-con-regla

I hope all of that helps you.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.