I have two tables, Table A and Table B.
Table A contains a column with primary keys assigned to specific users.
- USER1: abcd-efg-hij
- USER2: efg-hij-klm
- USER1: jdgd-fgsy-jdsfs
A single user can have multiple unique keys.
Table B contains keys that originate from Table A but with added extensions. For example:
- abcd-efg-hij-kjfekbk-djbbjf-mnjd → CHINA
- abcd-efg-hij → AUSTRIA
- efg-hijwrd-sajd-wqeye → INDIA
- efg-hij-klm-sdfbksdb-dfshd → MEXICO
- hij-klm → BRAZIL
- hij-klm-kbfekbfk → CHILE
Goal
I want to set up Row-Level Security (RLS) so that when a user logs in:
- Table A filters the keys based on the assigned user.
- Table B should be filtered to include any rows where the key contains a partial match with the keys from Table A.
Example
If USER1 has the key abcd-efg-hij in Table A, then Table B should be filtered to include:
- abcd-efg-hij-kjfekbk-djbbjf-mnjd → CHINA
- abcd-efg-hij → AUSTRIA
I used `CONTAINSSTRING`, but the `MAX` function restricts the search to a single key at a time, preventing the expected outcome. It worked fine when a user had only one key, but for users with multiple keys, it only considers the maximum value, causing incorrect filtering.
----------------------------------------------------------------------------------------
Filter_Territory_Keys =
VAR _user_terr =
CALCULATE(
MAX(TABLE_A[KEY]),
TABLE_A[EMAIL] = USERPRINCIPALNAME()
)
VAR _filter_for_str =
CONTAINSSTRING(
MAX(TABLE_B[KEY]),
_user_terr
)
RETURN
IF( _filter_for_str, MAX(TABLE_B[COUNTRY]), BLANK())
---------------------------------------------------------------------------------------------------------------------
Please suggest any possible solution.
