RLS

[Anonymous]

Hi,
I  am facing some issue while implementing the RLS for below fact table.

main_fact_table

job_no	year
IT123	2023
IT123467	2024
IT235	2023
IT2345	2024
IT345	2023
IT334	2024
IT3647	2023
IT3654	2024
IT6584	2023
IT6574	2024

user_access

user name	job_no	year
sourav	IT12	2023
jeet	IT6	2023
kunal	IT6	2024
sourav	IT3	2024
jeet	IT2	2023
kunal	IT1	2024
rupam	IT3	2024
rupam	IT6	2023

as i mentioned there is two table.

1)main_fact_table  (this is the main facr table)
2)user_access  (this is the details of user who has access to the perticuler data)

these two table are disconnected table hence they have no relation between them, and we are using the coloumn "job_no" (user_access) to decide the access for a perticuler user but in the "user_access" table column "job_no" dose not contain the full value we need to implement wildcard here for example in user_access  one user named "sourav" has access to the row  job_no in fact table  whose value starts with  "IT12" AND "IT3" so sourav should she all the rows in "main_fact_table" whose "job_no" starts with "IT12" AND "IT3" and corrosponding year also should take in considaration

in this case sourav should see in "main_fact_table" as below shown

job_no	year
IT123	2023
IT334	2024
IT3654	2024

[Anonymous]

Hi @Anonymous ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create a fuzy match table 

Table = 
ADDCOLUMNS (
    'user_access',
    "job_no2",
        CALCULATE (
            MAX ( 'main_fact_table'[job_no] ),
            FILTER (
                'main_fact_table',
                IFERROR ( SEARCH ( 'user_access'[job_no], 'main_fact_table'[job_no], 1, 0 ), 0 ) > 0
                    && 'main_fact_table'[year] = 'user_access'[year]
            )
        )
)

2. Create a role and add a filter with the formula:

[job_no] IN CALCULATETABLE(VALUES('Table'[job_no2]),FILTER('Table','Table'[year]=[year]&&'Table'[user name]=USERNAME())) 

Best Regards

[Anonymous]

Thanks, for the effort but there is a small changes in the requirement 



i want the third row as well for user named "sourav" to have access in the 'main_fact_table' cause in the 'user_access' table "sourav" has access to the "job_no" in 'main_fact_table' whose value starts with IT3 for the 'year'= 2024 as well 

Final access for sourav i have attached in the screenshot.

[Anonymous]

need help kindly find the solution