1

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?

Anant_Kumar
  • 764
  • 1
  • 7
  • 23
cjjw
  • 13
  • 3

1 Answers1

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])

Joao Leal
  • 5,533
  • 1
  • 13
  • 23
  • Luckily, there are no overlaps. Your solution is brilliant! – cjjw Dec 04 '20 at 13:30
  • Well, while there are no overlaps right now, there is no guarantee there won't be in the future as new customers are added. @JoaoLeal Could you provide a sample DAX expression to split a string by "|" to populate rows in a table? – cjjw Dec 10 '20 at 14:40
  • Add a calculated column called Customer Piped = "|" + [Customer] + "|", then on your security string make sure it ends with a pipe as well. And finally do the containsstring against your new calculated column. – Joao Leal Dec 11 '20 at 17:38
  • 1
    I made a small tweak using Customer Marked = "^" & [Customer] & "^" and passed in West|^Echo2^ and it filtered out Echo. Thanks a lot! – cjjw Dec 11 '20 at 20:11