Required Dax query to be get applied in manage roles for RLS(dynamic)

[SrikanthD013]

RLS need to be applied it must filter company code,segment code and department code

Tables i have,Base(fact) table which has columns (company code,segment code and department code)

base table(comp code) to company store table (comp code) many to one

base table(segment code) to segment table (segment code) many to one

base table(dept code) to segment table (dept code) many to one

Role(Role id) table which has all role ids,

Role company store(roleid,company id) which has details about store codes and role id

above two related one to many.

company store(company id,company code) table has store codes , role company and company store are related many to one , cross filter direction both.data is filtering company stores.

Similiarly tables have segment(segment id) , role segment(role id , segment id),related one to many. cross filter not able to apply both throwing error

and department ,role department tables

[v-jtian-msft]

Hi,@SrikanthD013 .I am glad to help you.

According to your description, you want to configure RLS for your report, given that there are multiple scenarios for the model relationships you have given.
I think you could use the LOOKUPVALUE function to find the data that matches the specified condition from the relevant table.
Also use the FILTER function to frame the range of DAX calculations
Here are my suggestions, note that my suggestions are only for reference, as DAX depends on the computing environment, you need to write your DAX code according to your actual environment (complex table relationships and field filtering logic), in order to specify the correct RLS filtering statements.

Here is the dax code:

// Filter  Role Company Storage Table
[Company Code]
    IN SELECTCOLUMNS (
        FILTER (
            'Role Company Store',
            'Role Company Store'[RoleID]
                = LOOKUPVALUE ( 'Role'[RoleID], 'Role'[UserPrincipalName], USERPRINCIPALNAME () )
        ),
        "Company Code", 'Role Company Store'[Company Code]
    ) 
// Filter  Role Department table
        && [Segment Code]
            IN SELECTCOLUMNS (
                FILTER (
                    'Role Segment',
                    'Role Segment'[RoleID]
                        = LOOKUPVALUE ( 'Role'[RoleID], 'Role'[UserPrincipalName], USERPRINCIPALNAME () )
                ),
                "Segment Code", 'Role Segment'[Segment Code]
            ) 
// Filter  Role Department table
                && [Department Code]
                    IN SELECTCOLUMNS (
                        FILTER (
                            'Role Department',
                            'Role Department'[RoleID]
                                = LOOKUPVALUE ( 'Role'[RoleID], 'Role'[UserPrincipalName], USERPRINCIPALNAME () )
                        ),
                        "Department Code", 'Role Department'[Department Code]
                    )

Here are related articles on RLS setup that I hope you find helpful
URL:
Solved: Dynamic RLS with Complex Hierarchy - Microsoft Fabric Community
Static and dynamic Row-level-Security in Power BI - Syntera
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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