RLS / Multiple Accesses per User

[Oceans88]

Dear all,

Trying to resolve the following issue. 

1- In My User Access table i have pretty much 2 cloumns "EMAIL" / "INTEREST"

2- In my Fact table i have a concatenation of all multiple columns called "RLS" ( Example: one row will contain: "24532-France-Acessories-Products"

3- What i want to try to achive is that RLS will search that "RLS" column in my fact table thus filtering the nessecery items.

4- Each user might have access to multiple arease ( E.g. Countries, product groups, etc....)

This was one of my best tries and worked until a user has multiple values(It will give an Error). Here i have not gotten to the part of searching a string bacuase at this point"RLS" has only one value

VAR USER_Object = 
calculatetable(
Values('55_Subscription'[Interest]),
'55_Subscription'[Email] = USERPRINCIPALNAME()
)

VAR RLS= 
Switch(TRUE(),
USER_Object = "ALL", TRUE(),
[RLS] = USER_Object, True(),
False()
)
Return
RLS

This one just didnt return anything. 

[RLS]
    IN CALCULATETABLE (
        VALUES ('55_Subscription'[Interest]),
        FILTER (
            ALL ('55_Subscription'),
            [Email] = USERPRINCIPALNAME()
        )
    )

Anyone have any idea on how i can solve? 

Thank you

[Oceans88]

Here is where im at, but im a bit stuck. When i run it gives me the error of:

"Table of multiple values was supplied where a single value was expected"

CONTAINSSTRING([RLS],
CALCULATETABLE (
        VALUES ('55_Subscription'[Interest]),
        FILTER (
            ALL ('55_Subscription'),
           CONTAINSSTRING( [Email], USERPRINCIPALNAME())
        )
    )
)

[Oceans88]

Update:

CONTAINSSTRING([RLS],
CALCULATETABLE (
        FIRSTNONBLANK ('55_Subscription'[Interest],1),
        FILTER (
            ALL ('55_Subscription'),
           CONTAINSSTRING( [Email], USERPRINCIPALNAME())
        )
    )
)

Ok Heres my progress, i was able to get it to work using CONTAINSSTRING by using FIRSTNONBLANK, but due to this im only able to see the first filter on RLS only so for example: If i should see 2 countrys eorth of data im only seeing one. DOes someone know how toovercome this? 

Thank you & Regards 

[v-rzhou-msft · Accepted solution]

Hi @Oceans88 ,

If there are multiple condtions in your data model, I suggest you to combine them in one column as the key column.

For example, format should look like "UserID - Country - Acessories - Products".

My Sample:

55_Subscription:

Data:

Relationship:

Code in Manage roles:

[Email] = USERPRINCIPALNAME()

[Combine Key] =
SWITCH (
    TRUE (),
    [Combine Key]
        IN CALCULATETABLE (
            VALUES ( '55_Subscription'[INTEREST] ),
            FILTER ( '55_Subscription', '55_Subscription'[Email] = USERPRINCIPALNAME () )
        ), [Combine Key],
    BLANK ()
)

Result is as below.

View as User A:

View as User B:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oceans88]

Hi Rico, 

Million thanks for your time and effort for looking into this. Very Helpful info. Appologize for the delay. Tuff work week. This indeed works. But what if i would want to instead make search inside of that "Combine Key" Column that you have created for a specific string. 

Imagine that this is my subscription table below and i would just release the data upon finding that interest keyword withing "Combine Key". 

THank you & regards

[Oceans88]

Just an Update, the following code is actually working to eliminate the issue of "multiple values when single value was excpected. 

[Region] //I changed this to a region filter (Non-concatenated) and it worked. 
    IN CALCULATETABLE (
        VALUES ('55_Subscription'[Interest]),
        FILTER (
            ALL ('55_Subscription'),
            [Email] = USERPRINCIPALNAME()
        )
    )

Now i need to add a search function and use [RLS] column in fact table (which contains concatenated values). Maybe i can add a Containsstring function somewhere in there? 

Any ideas?

Kr

[Oceans88]

Any ideas on what i could do to resolve ?