Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table called Cost_Centre, within that table I have a column called UPN which is the UPN of the cost_Centre manager. This column can have multiple values in it. For example I have placed the following in the UPN colum :
If I login as Mark@abc.com - I want to only see rows that Mark can see . I have created a role and added a filter on the UPN column - and was hoping to restrict rows based on the UPN value in the column.
This works when I only have a single user in the UPN column - but sometimes I need more than one person :
When logged in as Mark this works with Mark@abc.com as the only value in the UPN column for the row :
=dim_CostCentre[UPN] = USERPRINCIPALNAME()
So I have tried this with the string with multiple users - and cannot make it work :
IF(CONTAINS('dim_CostCentre',dim_CostCentre[UPN],USERPRINCIPALNAME())
When logged in as Mark @abc.com no rows are visible. I think the contains is working on the column and not the row. Any ideas on how I can make this work.
Thanks
Try this instead for your RLS filter. If performance poor, you could alway split your text into a list with Text.Split and expand that list to new rows and use your simpler filter expression.
CONTAINSSTRING(dim_CostCentre[UPN], USERPRINCIPALNAME() )
Pat
Hi Pat
Went back to CONTAINSSTRING and worked perfectly as far as I have tested it.
My issues was I was running on Analysis Services 2017 - upgraded to 2022 and it worked.
I think 2019 would work as well.
Cheers
Hi Pat
Tried that - unfortunately my Visual Studio does not recognise that DAX function. I am not sure if it relates to my version of Analysis Services - which is SQL 2017. I was going to upgrade and see if CONTAINSSTRING will be accepted, is it a relatively new function . Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |