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

[maryg]

Hi - I am struggling to find an efficient way to create a data set from 2 unrelated tables (they cannot be related), with an extract of substring.  I am able to successfully pull the substring using this, but it literally takes days to run for only 30k rows.  I would love some advice to make this more efficient.  Thanks!

[rajendraongole1 · Accepted solution]

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.

[Anonymous · Accepted solution]

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. 

[rajendraongole1 · Accepted solution]

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.