Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
User | Count |
---|---|
50 | |
24 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |