Row level security and permission to view 'All'

[Matti]

Hello,

Using the following blog post I've been seting up Dynamic RLS.

Different approach to Dynamic Row Level Security

So far everyone has a seperate row in the security table for each department or country that they have permission to view. However, for user who should be allowed to view all countries or all departments this creates a inconvenience, given that the company has a lot of departments and countries, and as such would require a long security table.

Is there anyway to give users permission to all departments or countries without listing the user and all departments in the security table? Preferably i could just write 'All' in the security table and PBI would then read the entry as equal to all departments.

See the picture below for an simple illustration of what i am looking for.

[v-chuncz-msft]

@Matti,

You may add a CONTAINS Function.

CONTAINS (
    Table1,
    Table1[Email], USERPRINCIPALNAME (),
    Table1[Department], "All"
)
    || 

[v-chuncz-msft · Accepted solution]

@Matti,

You may replace "All" with a blank space, and note the logical OR operator (||) in the formula.

[Matti]

Hi v-chuncz-msft,

My problem is that we don't have a department hierarchy with 'All' on the top, so Power BI won't recognize 'All' as something recognizable in the department dimension. The hierarchy is flat so to speak, so it's just department 10, 20, 30..., 90. Other than that, I think the code in the blog post that I link to does somewhat the same as what your code suggests?

I need to know if there is anyway to make Power BI give access to all departments. I originally hoped leaving a blank space "" instead of "All" in the security table would work.

NOT(ISBLANK(LOOKUPVALUE(security_mapping[CATEGORY],security_mapping[CATEGORY],[Category],security_mapping[USER],USERNAME())))