Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
diegob73
New Member

DAX CONTAINSSTRING

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?

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

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.

 

View solution in original post

2 REPLIES 2
123abc
Community Champion
Community Champion

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.

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors