RLS - DAX/Structural doubt - How to properly implement this RLS Role?

[RicardoV2]

Hello,

I'm importing an Excel file to my PBIX, to implement RLS. The idea is GlobalUsers like 3 and 4 (belonging to ICC_GI and ICC_GR AD groups) to  have access to everything, while SpecificUsers (from ICC_G1 AD Group) like 1 can only have access to India/CT1 and KL/CT1 and user 2 like China/CT1 and Singapore/CT2.

The resulting table is called UserPermissions, and it is connected to factual factResults through the sk/fk_Location_CTs
(Relationship is n:n, as keys will be repeated, but direction is offcourse from UserPermissions to the factual)

So, for the implementation of the role I'm using the following DAX, which is working correctly for ICC_G1 users with specific access, 
but for the ICC_GI and ICC_GR users it isn't working 100% as I would like. I've noticed that, for these GlobalUsers, all the existing specific relationships are made available, which in this case would mean that GlobalUsers will have access India/CT1, KL/CT1, China/CT1 and Singapore/CT2. This for me might create a problem if a combination of Location and Campaign Type exists only on the factual (because UserPermissions are still being constructed on Excel 😞 ), GlobalUsers can't get access to these entries.

So, though understanding what I ask is questionable (as well as having a fact table without all relationships to dim UserPermissions in place), I ask is if it's possible to simply make all factResult entries available to GlobalUsers?

VAR CurrentUser = USERPRINCIPALNAME()

RETURN
    IF(
        LOOKUPVALUE(UserPermissions[AD Group], UserPermissions[User], CurrentUser) IN {"ICC_GI", "ICC_GR"},
        TRUE(),
        IF(
            LOOKUPVALUE(UserPermissions[AD Group], UserPermissions[User], CurrentUser) = "ICC_G1" &&
            UserPermissions[User] = CurrentUser,
            TRUE(),
            FALSE()
        )
    )

Thanks in advance

[RicardoV2]

Hello @Anonymous , thanks for suggesting but it doesn't work.
As a condition this can only return true or false, but again, as this enables all existing and valid relationships, only the (all) existing specific relationships will be enabled.

[Anonymous]

Hi, @RicardoV2 

Maybe you can try the following DAX:

VAR CurrentUser = USERPRINCIPALNAME()

RETURN
    IF(
        LOOKUPVALUE(UserPermissions[AD Group], UserPermissions[User], CurrentUser) IN {"ICC_GI", "ICC_GR"},
        ALL(factResults),
        IF(
            LOOKUPVALUE(UserPermissions[AD Group], UserPermissions[User], CurrentUser) = "ICC_G1" &&
            UserPermissions[User] = CurrentUser,
            TRUE(),
            FALSE()
        )
    )

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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