RLS to set a default but switchable set of filters

[soldstatic]

I'm trying to implement RLS in a PBI file where we have a hierarchy that basically amounts to our org chart. My intent is to have the file look up the user's preferences in a table, and if the user has set a preference that they want their default view to be for a particular part of the org, then that is what they will get. We actually will maintain this table based on the actual org chart so as people change roles their view changes for them automatically (don't worry I've got an override mechanic etc so it doesn't get annoying). 

The main slicer that I am trying to filter is fed from a table with 44 records that represents each of our territories and whatever orgs they belong in, basically:

Center	Division	District	Area	SearchableID
Blue Springs	Metro Division	Johnson District	East Area	,1,777,421,523,235,
Someplace	Southwest Division	Jackson District	East Area	,1,777,422,253,3416,
Else	Northwest Division	Clay District	West Area	,1,888,456,987,321

Note the serachable ID field is a string where an ID for each of the levels is present. So "East Area" is always 777, and each record has a ",1," in it no matter what.

I have a table where I'm storing the user preferance / location in our org chart. This table has an ID representing whichever "level" of the hierarchy they want to subscribe to. So someone subscribed to Blue Springs only might have a 235, but someone subscribed to East Area would have a 777.:

East_Area_Director@domain.com	777
someone@domain.com	182
Blue_Springs_Manager@domain.com	235

Using RLS I am able to filter the preference table to get down to my user and their ID (if they have one set). But from here I'm stuck. I thought I could add a field to the slicer table called something like "view" and this would have two values, "Default" or "Company". If a user doesn't have the setting then everything would be Default. If the user had a setting present then the areas they wanted to see by default would have "Default" in the view column and the other areas would be "Company". This is the field I added but because it's a column it doesn't work w/ RLS:

Default View = 
var __ParentID = FIRSTNONBLANK(UDM_SUBSCRIPTIONS[PARENTID],[PARENTID])

return
if(countrows(UDM_SUBSCRIPTIONS)=1,
    if(
        CONTAINSSTRING(ServiceCenterHierarchy[SearchableID],__ParentID)
        ,"Default"
        ,"Evergy")
    ,"Default O")

I know I could switch this over to a measure, but I can't add a measure to the filter pane so I'm not sure how that's supposed to work? 

Thanks in advance!

[lbendlin]

You cannot use RLS for that. However, you can publish your report with default filters, or add filters to the report URL.  That will give users the sefault settings and will allow them to modify filters.

[soldstatic]

Hm I don't accept that, surely there is a way to do this with RLS...

The default is no filter on the slicing table. But depending on user preferences we would apply the optional filter.

I can think of two very poor solutions to this, but was really hoping there was a simpler method.

1. Make a power app and re-do the entire PBI file into powerapps by embedding one bloody visual at a time, then applying parameters that way
2. Create a cross join of the slicer table against the table of user settings, using that to create a giant table with every permutation of slicer and user that RLS would then filter down. 

[lbendlin]

There is a fundamental philosophical and technical difference between RLS and (customizable) filtering. RLS is inherently restrictive. You cannot "back out of" a properly implemented RLS, you can only make it worse (by adding more filters)  Customizable filters on the other hand can be voided or changed. 

RLS = kindergardening

Filters = responsible adulting

[soldstatic]

I don't understand why one is kindergarden and one is responsible adulting. They certainly have different intentions in their functionality. PowerBI has a lot of 'self service' functionality for those who are "good with computers", such as setting up your own default filters/bookmarks/views. The problem is my users aren't always that good so I need to manage their default views for them. The only way I can figure out how to do that so far is with this crossjoin + RLS approach, which so far seems to work. It does require you to have a table of all of your users' emails in it though which is rather annoying.

[lbendlin]

RLS is restrictive - there is no way out, ie you cannot change it to be less restrictive, only more.  Default filters are a suggestion, allowing users to focus when they want, and to expand their field of view when needed.  

The purpose of RLS is to protect data.  The purpose of default filters is to help users focus.

[soldstatic]

Yea but it only restricts tables you set it to, so I don't see a philosophical reason why this approach should not be employed. 

I'll tell you so far I have it in with the cross join method and it works pretty well. I have a few busted measures that I'm trying to wrap my head around but I've got like 75% functionality with this approach and the other 25% I will figure out I just haven't got my head wrapped around how to approach it. 

The measures I'm having trouble with are what I call my "context determining measures". Basically I look at the data in the report to determine what level of the hierarchy the user is intending to look at. It assumes that all users come into the report from the 'company' unfiltered level, and then checks to see if the table is filtered on certain columns in order to determine the context the user is after. Old measure below, but in short the "isfiltered()" doesn't work since the field that is being checked is not filtered. So I just need to come up with a new method to determine what the user is viewing. 

GeoHierarchy Max Type = 
CALCULATE(
    if(HASONEVALUE( ServiceCenterHierarchy[Jurisdiction] ) && ISFILTERED(ServiceCenterHierarchy[Jurisdiction])
        ,"Jurisdiction"
        ,IF(HASONEVALUE('ServiceCenterHierarchy'[Service Center]) && isfiltered(ServiceCenterHierarchy[Service Center])
            ,"Service Center"
            ,if(HASONEVALUE(ServiceCenterHierarchy[Division]) && ISFILTERED(ServiceCenterHierarchy[Division])
                ,"Division"
                ,if(HASONEVALUE(ServiceCenterHierarchy[District]) && isfiltered(ServiceCenterHierarchy[District])
                    ,"District/Region"
                    ,if(HASONEVALUE(ServiceCenterHierarchy[Area]) && isfiltered(ServiceCenterHierarchy[Area])
                        ,"Area"
                        ,if(HASONEVALUE(ServiceCenterHierarchy[Legacy Area]) && isfiltered(ServiceCenterHierarchy[Legacy Area])
                            ,"Legacy Area"
                            ,"Company"
                        )
                    )
                )
            )
        )
    )
    ,CROSSFILTER(QualifiedEventList[BeginDateOnly],DateTable[CALENDAR_DATE],None)
)