Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kv
Advocate I
Advocate I

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


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-... 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 

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

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.

View solution in original post

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

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.

That's brilliant @sturlaws  Thank you. 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors