Filtering a column based on delimited text

[bstripp]

I am trying to create a filter that would limit rows based on a delimited string passed into CUSTOMDATA()

I've found some samples of how to delimit text, but for the life of me I can't figure out how to apply that kind of DAX in the filter section of the PowerBI section.

Example:
Say I have the following table:

Country	Widgets
GERMANY	100
FRANCE	75
UNITED STATES	60

I create a role: EU_PLANNING

I want to have a filter on [COUNTRY] that would be passed through CUSTOMDATA()

So when I pass this:
CUSTOMDATA() = "GERMANY,FRANCE"

Row Level Security then filters the data set to this:

Country	Widgets
GERMANY	100
FRANCE	75

I've looked at the following code, but I can't figure out how I would apply it to an actual filter.  From (https://community.powerbi.com/t5/Desktop/DAX-how-split-a-string-by-delimiter-into-a-list-or-array/m-...)

DebugList = 
VAR mymeasure =
 SUBSTITUTE ( USERNAME(), ",", "|" )
VAR Mylen =
 LEN ( mymeasure )
VAR mytable =
 ADDCOLUMNS (
 GENERATESERIES ( 1, mylen ),
 "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
 )
VAR mylist =
 SELECTCOLUMNS ( mytable, "list", [mylist] )
RETURN
 CALCULATE ( COUNTROWS ( Table1 ), Table1[ID] IN mylist )

I get the changing the CSV into a list called mylist.  But can I filter a column in RLS against a list and if so what should I be returning?  I tried to return just [mylist] but that doesn't seem to work.

[Anonymous · Accepted solution]

If you have a table with a column [Country], then this filter does filter for the passed in countries in CUSTOMDATA();

// Filter for the Country column
=SEARCH( [Country], CUSTOMDATA(), 1, -1) > 0

You can also use the CONTAINSSTRING function. It'll be even easier.

Best

D

[bstripp]

Okay the SEARCH function is brilliant.  I will be simplifying based on that.  I like your solution a lot better as it's easier to understand.  However, for anyone looking at this later...

However, I was wrong, I could return the list using in.  I just had to change the DAX to account for STRINGS instead of NUMBERS

[CAT] in VAR mymeasure =
SUBSTITUTE ( [Measure 4], "\", "|" )
VAR Mylen =
LEN ( mymeasure )
VAR mytable =
ADDCOLUMNS (
GENERATESERIES ( 1, mylen ),
"mylist", PATHITEM ( mymeasure, [Value] ) )
VAR mylist =
SELECTCOLUMNS ( mytable, "list", [mylist] )
return mylist