undefined

[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

[Anonymous]

Thanks much appreciated it worked.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

When applying to multiple users, you can replace "sourav" with USERPRINCIPALNAME().

Test =
var _table1=
SUMMARIZE(
    FILTER('user_access','user_access'[user name]="sourav"),
    [job_no],[year])
var _table2=
DISTINCT('main_fact_table'[job_no1])
var _table3=
CROSSJOIN(_table1,_table2)
var _table4=
ADDCOLUMNS(
    _table3,"test",CONTAINSSTRING([job_no1],[job_no]))
var _max=
MAXX(
    FILTER(
       _table4,
        [test]=TRUE()&&[job_no1]=EARLIER('main_fact_table'[job_no1])&&[year]=EARLIER('main_fact_table'[year])),
        [job_no1])
return
IF(
    _max=BLANK(),FALSE(),TRUE())

2. Modeling – Manage roles – Create – [Test].

3. Result:

Best Regards,

Liu Yang

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

[Anonymous]

showing the follwing error while replacing user hardcoded name to userprincipalname()