Using a column of values with CONTAINSSTRING

Question

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

parry2k · Answer

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?
&nbsp;
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 · Answer

Sure thing&nbsp;parry2k&nbsp;&nbsp;The 4 tables involved:&nbsp;Grid Data&nbsp;ModelVersionIDElement Name100Built-in Bar100Existing Wall2100Existing Wall3101Door Frame (E)101Desk101Generic Wall102Built-in Bar102Existing Wall2102Carpeting&nbsp;Model Version&nbsp;IDModelID100681016810269&nbsp;ModelID6869&nbsp;&nbsp;Search Term&nbsp;ModelIdSearch terms68built68(E)69Rail69missing69generic&nbsp;Relationships&nbsp;Model&nbsp; Model VersionId 1: * Model Id&nbsp;Model Version&nbsp; &nbsp;Grid DataId 1: * ModelVersionId&nbsp;Model&nbsp; Search Term&nbsp;&nbsp;Id 1: * Model Id&nbsp;The DAX expression I'm needing is for a new Calculated Table.&nbsp;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.&nbsp;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).&nbsp; 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).&nbsp;Once the filtering is done for Model Id 68 the expression should move on to Model Id 69 and repeat the same steps.&nbsp;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.&nbsp;Mike&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
Please show the your final expected result.

mlee · Answer

The final output would be:&nbsp;ModelVersionIDElement Name100Built-in Bar101Door Frame (E)102Existing Wall 2

v-juanli-msft · Answer

Hi&nbsp;mlee&nbsp;
For 69, it contains "Rail, missing, generic" ,&nbsp;

ModelId
Search terms

68
built

68
(E)

69
Rail

69
missing

69
generic

&nbsp;
69&nbsp;corresponds to Model Version ID's of 102, 
but the&nbsp;Element Name of 102 contains
"Built-in Bar,Existing Wall2, Carpeting",
we can't find any of&nbsp;"Rail, missing, generic" in&nbsp;"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

&nbsp;
Best RegardsMaggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Using a column of values with CONTAINSSTRING

8 Replies

Related content

Switch -Containsstring

Containsstring and Count

NOT(CONTAINSSTRING) - multiple values

Splitting Column values

Measure with IF( CONTAINSSTRING

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