Creating a calculated table, from unrelated tables AND a substring extract

Hi maryg - If the tables are large and the matching process is slow due to the dataset size, consider breaking down your dataset into smaller chunks and processing them separately.  also please share the sample data for review .

you can try below approach with variables and check once.

 

NPNextract =
VAR SearchText = 'August52024'[Case Narrative]
VAR NPNList = 'RTS_IVL_06_25_2024'[NPN]

RETURN
MAXX(
FILTER(
ADDCOLUMNS(
NPNList,
"IsMatch", CONTAINSSTRING(SearchText, [NPN])
),
[IsMatch]
),
[NPN]
)

 

Hope this helps.

Hi maryg ,

 

Thanks rajendraongole1  for the quick reply. I have some other thoughts to add:
Based on your DAX it looks like you are creating a calculated column.
Measures have better performance as compared to calculated columns, please create a measure according to the following formula.

MEASURE =
CALCULATE (
    MAX ( 'RTS_IVL_06_25_2024'[NPN] ),
    FILTER (
        'RTS_IVL_06_25_2024',
        CONTAINSSTRING (
            MAX ( 'August52024'[Case Narrative] ),
            'RTS_IVL_06_25_2024'[NPN]
        )
    )
)

Best Regards,

Neeko Tang

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