Forum Discussion
Using a column of values with CONTAINSSTRING
Super Usermlee 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
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.
The final output would be:
ModelVersionID Element Name 100 Built-in Bar 101 Door Frame (E) 102 Existing Wall 2 
v-juanli-msftCommunity Support
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
MaggieCommunity 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.
