Forum Discussion

diegob73's avatar
diegob73
New Member
2 years ago
Solved

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 ...
  • 123abc's avatar
    123abc
    2 years ago

    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.

     

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    diegob73
    New Member
    2 years ago

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