Dynamic RLS - Role based on multiple combination between two Dim table

[Anonymous]

Hi, I need to implement dynamic RLS where I have two Dimension table Platform and Channel .

The RLS is based on various combination of Channel Group / Channel and Platform.

Below is my Dim and fact table 

 RLS table:

Username	Channel Group	Channel	Platform	Comments
user1@domain1.com	A		Tiktok	Channel Group = A and platform = Tiktok
user2@domain1.com	A	Social		Where Channel Group = A and Channel = Social
testuser@gmail.com	C	Search	All Platform	All platform for Search
testuser@gmail.com	C	Display	All Platform	All platform for Display
testuser@gmail.com	C	Marketing	Meta

Under Manage role, I define my  filter on Channel table as:

(Channel[Channel]) IN

    SELECTCOLUMNS(

        FILTER(

            'RLS',

            RLS[Username] =  USERPRINCIPALNAME()

        ),

        "Channel",

        [RLS Channel]

    )

And same on Platform table:

(Platform[Platform Name]) IN

    SELECTCOLUMNS(

        FILTER(

            'RLS',

           RLS[Username] =  USERPRINCIPALNAME()

        ),

        "Platform Name",

        [RLS Platform]

    )

But It doesn't work for user1 and testuser . 

Could someone please help here to define RLS for testuser where he should see all platform for some channel and selected platform for other channel.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try to modify your formula like below:

Channel Table filter:

(Channel[Channel]) IN
    SELECTCOLUMNS(
        FILTER(
            'RLS',
            RLS[Username] = USERPRINCIPALNAME() &&
            (RLS[Platform] = "All Platform" || RLS[Platform] = Platform[Platform Name])
        ),
        "Channel",
        [RLS Channel]
    )

Platform Table filter:

(Platform[Platform Name]) IN
    SELECTCOLUMNS(
        FILTER(
            'RLS',
            RLS[Username] = USERPRINCIPALNAME() &&
            (RLS[Channel] = "All Channel" || RLS[Channel] = Channel[Channel])
        ),
        "Platform Name",
        [RLS Platform]
    )

Best Regards,
Adamk Kong

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

[Anonymous]

Could anyone help here please? I can provide additional information incase the query is not clear