DAX CONTAINSSTRING

[diegob73]

Hello good afternoon, I need help with a DAX formula. I have two tables, in one of them a column of text describing a product (car), make, model, type of transmission, etc. In another table I have a list of possible models.

I'd like to add a column to the first table with just the models of each car. I understand that I should use the CONTAINSSTRING function, but how can I avoid passing the models to the function one by one?

That is, pass the column of the second table as the second argument, and compare it with each of the rows.

Is there a way to do it?

[123abc · Accepted solution]

Yes, you can achieve this using DAX and a combination of functions like RELATED, FILTER, and CONTAINSSTRINGX. Here's how you can create a calculated column in your first table to extract the models:

ModelColumn =
VAR CurrentCar = 'FirstTable'[Description] // Assuming 'FirstTable' is your first table with the column 'Description'
RETURN
CONCATENATEX (
FILTER (
'SecondTable',
CONTAINSSTRING ( CurrentCar, 'SecondTable'[Model] ) // Assuming 'SecondTable' is your second table with the column 'Model'
),
'SecondTable'[Model],
", "
)

Here's how the formula works:

• VAR CurrentCar: This variable holds the current car description from the 'FirstTable'.
• FILTER: It filters the rows of 'SecondTable' based on whether the model appears in the current car description.
• CONTAINSSTRING: It checks if the current car description contains the model from 'SecondTable'.
• CONCATENATEX: This function concatenates the models found for the current car description into a comma-separated list.

Make sure to replace 'FirstTable', 'Description', 'SecondTable', and 'Model' with the actual names of your tables and columns in Power BI or Excel.

This formula iterates over each row in 'FirstTable' and extracts the models mentioned in the description of each car, aggregating them into a single column.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[diegob73]

Thanks a lot! The solution you gave me worked perfectly.