Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtering a column based on delimited text

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:

CountryWidgets
GERMANY100
FRANCE75
UNITED STATES60


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:

CountryWidgets
GERMANY100
FRANCE75


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-p/559615#M263888)

 

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's avatar
    Anonymous
    6 years ago

    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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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