topic undefined in Developer

undefined

Anonymous — Wed, 10 Jan 2024 12:40:03 GMT

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

Re: undefined

Anonymous — Fri, 12 Jan 2024 08:13:59 GMT

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

Re: undefined

Anonymous — Fri, 12 Jan 2024 10:08:46 GMT

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

Re: undefined

Anonymous — Fri, 12 Jan 2024 14:27:25 GMT

Thanks much appreciated it worked.