DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value

kv — Wed, 23 Oct 2019 21:31:11 GMT

Within a Power BI Role's DAX filter expression, is it possible to pass a local DAX variable that has a delimited string of values to an IN clause i.e [ColumnName] in { "Event A", "Event B", "Event C" }. [ColumnName] is one of the columns of the underlying imported data.

The DAX code checks ok but the filter does not work. No data is returned giving the perception that the IN clause did not like the passed delimited string variable.

I used the patterns mentioned on this post https://community.powerbi.com/t5/Desktop/DAX-how-split-a-string-by-delimiter-into-a-list-or-array/m-p/559615#M263888 Definitely very helpful and learnt a few more DAX concepts.

My Code looks like this:

[ColumnName] in
{

var IncomingString = "Program Name 1; Program Name 2"

var StringAsPath = SUBSTITUTE (IncomingString , ";", "|" )
var NoOfValues = PATHLENGTH (StringAsPath )
var MyTable =
ADDCOLUMNS (
        GENERATESERIES (1, NoOfValues
        "ProgramName", PATHITEM (StringAsPath, ''[Value], TEXT)
    )
RETURN
   CONCATENATEX (MyTable, [ProgramName], ",")
}

If I hard code the PBI Role's DAX filter as [ColumnName] in {"Program Name 1, Program Name 2"} then it filters the data correctly.

Please let me know what I might be doing wrong or whether this is not even possible inside a Power BI Role's DAX expression. Thanks in advance

TomMartens Zubair_Muhammad

Re: DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value

sturlaws — Fri, 25 Oct 2019 14:21:14 GMT

Hi kv ,

how about you change this

CONCATENATEX (MyTable, [ProgramName], ",")

to this

SUMMARIZE(MyTable, [ProgramName])

and drop the curly brackets

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Re: DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value

kv — Fri, 25 Oct 2019 19:31:13 GMT

That's brilliant sturlaws Thank you. :)

topic DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value in DAX Commands and Tips

DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value

Re: DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value

Re: DAX expression inside a PBI Role - Use string variable with delimited values for the IN clause value