How to create a complex filter from a single value passed in from USERNAME() function

Question

I am generating Power BI embedded tokens for Row Level Security.

The token contains a value of a role for the USERNAME() function to receive, which is then used in the Table filter DAX expression, e.g.: [Region] = USERNAME(). This works when a value (say West) is passed in from the token, and the sales report will be filtered by the West Region.

Next, I need to add additional filters like: [Region] = USERNAME() && [Customer] IN {"Delta", "Echo"}. I've tried to pass in West|Delta|Echo to USERNAME() and changed the DAX expression to: [Region] = LEFT(USERNAME(), FIND("|", USERNAME())-1) && [Customer] IN {"Delta", "Echo"}

This version worked, but the list for IN filter had to be hard-coded.

Is there a way to write a DAX expression to extract multiple values delimited by | and create a list for the IN operator shown above?

Answer 1

I'd have a table in the middle, and then link the appropriate fields to that table, so West|Delta|Echo would be 2 rows: West, Delta and West,Echo. Then this table has relationships (one directional) to the Region and Customer tables.

In line with what you are currently doing, and if there are no overlaps between the Customer entries (e.g., no Echo and Echo2), you can use CONTAINSSTRING(USERNAME(), [Customer])