Using a column of values with CONTAINSSTRING

[mlee]

I have the following DAX expression for a new calculated table:

FILTER(

'Grid Data',

AND(

RELATED('Model'[Id]) = 68,

OR(

CONTAINSSTRING('Grid Data'[Family Name],"(E)"),

CONTAINSSTRING('Grid Data'[Family Name],"built")

)

)

With the hardcoded values the table is generated just fine.

However, instead of hardcoding I want the expression to reference the following table instead:

Search Term Table

ModelId	Search terms
68	built
68	(E)
69	Rail
69	missing
69	generic

What I'd like to do is for each Model ID use the associated search terms against the Family Name column using CONTAINSSTRING. I was able to get a column of all the distinct search terms for a particular model but when I try using that column in CONTAINSSTRING it fails since the function is only expecting a single value in the find text parameter.

Does anyone know if this is doable? 

Thanks,

Mike

[v-juanli-msft]

Hi @mlee 

Is your data large?

I think i would add some columns or a new table which woild increase the data size and reduce the performance.

Best Regards
Maggie

[mlee]

The data could be large. It depends on how much filtering I perform upstream.

[parry2k]

@mlee it is possible, can you share data model? What are you trying to achieve? Do you want to create new table or some sort of measure. It is not clear what end result you are looking for?

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[mlee]

Sure thing @parry2k 

The 4 tables involved:

Grid Data

ModelVersionID	Element Name
100	Built-in Bar
100	Existing Wall2
100	Existing Wall3
101	Door Frame (E)
101	Desk
101	Generic Wall
102	Built-in Bar
102	Existing Wall2
102	Carpeting

Model Version

ID	ModelID
100	68
101	68
102	69

Model

ID

68

69

Search Term

ModelId	Search terms
68	built
68	(E)
69	Rail
69	missing
69	generic

Relationships

Model  Model Version

Id 1: * Model Id

Model Version   Grid Data

Id 1: * ModelVersionId

Model  Search Term  

Id 1: * Model Id

The DAX expression I'm needing is for a new Calculated Table.

The calculated table should be a subset of the Grid Data table. The DAX expression should filter based on the search terms for each model.

For example, for model 68 I have built and (E) for search terms. The expression should travel down to the grid data table and find all rows with that are associated with Model ID 68. In this case that corresponds to Model Version ID's of 100 and 101. Next the expression should look at all rows with Model Version ID 100 and 101 in Grid Data and look at the Family Name column and filter based on the search terms built and (E).  I'm using CONTAINSSTRING since I'm looking for partial text matches. For Model 68 two rows should be kept...the row with Built-in Wall and Door Frame(E).

Once the filtering is done for Model Id 68 the expression should move on to Model Id 69 and repeat the same steps.

This expression I wrote in my original post works if I hard code some values in. But I'm not sure how to make this Calculated Table work when filtering based on another table's columns.

Mike

[Ashish_Mathur]

Hi,

Please show the your final expected result.

[mlee]

The final output would be:

ModelVersionID	Element Name
100	Built-in Bar
101	Door Frame (E)
102	Existing Wall 2

[v-juanli-msft]

Hi @mlee 

For 69, it contains "Rail, missing, generic" , 

ModelId	Search terms
68	built
68	(E)
69	Rail
69	missing
69	generic

69 corresponds to Model Version ID's of 102,

but the Element Name of 102 contains

"Built-in Bar,Existing Wall2, Carpeting",

we can't find any of "Rail, missing, generic" in "Built-in Bar,Existing Wall2, Carpeting"

why the final output is ?

ModelVersionID	Element Name
100	Built-in Bar
101	Door Frame (E)
102	Existing Wall 2

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlee]

You are correct @v-juanli-msft . I made a mistake when I pasted in the final output. 

The corrected final output would be:

ModelVersionID	Element Name
100	Built-in Bar
101	Door Frame (E)