DAX results from Pathcontains() query into another Dax query with Containsstring() or Search()

[Anonymous]

Hey Everyone --- first of all I may be going about this all wrong.  I usually do tsql and don't have any problems sending results from a single column into another query ie:

select values from table where column in (select column from table)

So I am trying to do someting similar in DAX but I really have no clue what to do.  I searched online for all the TSQL to DAX comparison docs and found many people trying similar but not identicial things in the forum here, but I can't figure it out from those.

I have a DAX query like this (pulling from my PBI Dataset in Powerbi.com:

EVALUATE SUMMARIZECOLUMNS('Table1'[City],
FILTER (
VALUES ( 'Table1'[City] ),
PATHCONTAINS ( "1|City1|1|City2|1|City3|City4", 'Table1'[City] )))

And now I have another query like this:

EVALUATE SUMMARIZECOLUMNS('Table1'[City],
FILTER (
VALUES ( 'Table1'[City] ),
CONTAINSSTRING(Table1[City],"City4" )))

I put in bold about the 'parameter values' --- the problem I have is that they are dynamic and pathcontains() seems to work great with multiple values separated by pipe | but containstrings(), search(), find(), etc do not work with multiple values and the pipe |

I have made another query like this that works with containsstring() ---

EVALUATE SUMMARIZECOLUMNS('Table1'[City],
FILTER (
VALUES ( 'Table1'[City] ),
CONTAINSSTRING(Table1[City],"City4" ) || CONTAINSSTRING(Table1[City],"City1" )))

But we have multiple, dynamic values and wouldn't be able to predict ever scenario of how many values are or are not coming in at any given moment.

I searched through forums and docs online and find some uses of IN and some uses of OR and some uses of FILTER(ALL(CONTAINS(VALUES but I honestly really struggle to wrap my mind around all this.

To me from tsql it is simple... I take the multiple values and send them into the other query using IN ... but here I can't find the right combination to make this work after a couple days of trying.

Maybe you have some ideas?  I can try to elaborate more if needed, but I tried to keep this 'as simple as possible' for now.  There may be other ways outside of my thinking as well that is the right way to do something like this in DAX.  I really don't know.

Thank you

[Anonymous · Accepted solution]

So I was able to take a different approach this morning and arrived at the result that was needed.  I'll be sure to try and read all the referenced docs with all the requirements for 'quick' responses next time.  

Let me post more details here on the solution.  I had intentionally left out more details to keep the original post focussed and simple.  But we're connecting to a Power BI Dataset in a premium workspace using SSRS / PBIRS Power BI Report Builder for Paginated Report.  The problem we have is that our Paginated Report parameter is multiple values but inside some of those multiple values are comma separated lists of addiional multiple values (all of this gets pulled from a Sharepoint List).

So missing key was to create a duplicate column in PowerBI Desktop Power Query before creating the PBI Dataset. On the duplicate column we can split to rows on the comma delimiter which gives us the correct single value reference point for our Paginated Report parameter that comes in Val1 | Val2 | Val3 format which only Pathcontains() worked with.

See here.  Post can be marked resolved

So I’ll go quickly through the results here.

1. In PowerQuery Editor Create a duplicate column of the main column we’re working with, in my case ‘City’.  Name the Original Column CityLabel and the new Column CityValue.  Next let’s split the value of the CityValue column using the Split Column feature and make sure we go Advanced > Rows:

Now we have this which gives us what we need to make our PathContains work from Dax / Report Builder:

Update our dataset query:

Make Sure we still use our Join for parameter:

Notice when we run our report our available values still show only single values:

But when we run the report with single values we now get the comma separated results included since we’re summarizing/searching on our single value reference column:

I kept mixing up CityLabel and CityValue at certain points of my tests/screenshots, but I’m confident the above will get us where we need to be.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523