Pass array in dynamic RLS with USERNAME()

[charlottesmn]

Hello!

I have a report with a customer dimension table and an order fact table. The two have a relationship one to many.
CUSTOMER:

customer_id	customer_name	customer_last_name	customer_first_name
00000152	xx, yy	xx	yy
15478965	qq, oo	qq	oo
12365478	aa, ll	aa	ll

FACT:

order_id	customer_id	order_date	...	...
123654	00000152
789654	15478965
147852	12365478

This report is embedded and I have set up dynamic RLS on the customer table as: customer_id = USERNAME() such that when a customer logs in to the website its username is returned and passed, filtering the customer table which filters the fact table with its order.

I now have a use case where instead of returning a single customer_id, the USERNAME() function will return a list of IDs (for example IDs it has been assigned to in the past because of some merged ids). How can I update the RLS to work with an array? 

For example, when customer 00000152 logs in it is associated with [00000152, 15478965] IDs and so both IDs should be visible. 

Thank you!

[Anonymous]

Hi @charlottesmn ,

Given your scenario, you'll need to adjust your RLS formula to accommodate multiple IDs.

You'll need to use the function in combination with a delimiter to separate IDs in the string returned by . Assuming returns a string like "00000152,15478965", you can use the following DAX expression:

CONTAINSSTRING(CONCATENATE(",", USERNAME()), CONCATENATE(",", [customer_id], ","))

CONTAINSSTRING function (DAX) - DAX | Microsoft Learn

USERNAME function (DAX) - DAX | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

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