topic Re: Search a substring in string from another table with DAX in DAX Commands and Tips

Search a substring in string from another table with DAX

Anonymous — Wed, 10 May 2023 13:26:29 GMT

Hi everyone,

I need help figuring out how to search for a substring in another table with DAX. My data looks like this:

Table 1:

Customer ID	Products IDs
AA_019442	123456A;123456B
BB_149432	123456A
CC_924742	123456F;123456C
DD_127194	123456C
EE_373878	123456E

Table 2 (unrelated and unique ids):

Product ID	Description
123456A	AAAAAAAAAAA
123456B	BBBBBBBBBBBBB
123456C	CCCCCCCCCCCC

I need to compare every substring in the column "Product IDs" in Table 1 with the column "Product ID" in Table 2.

My expected outcome would be:

If both subtrings match, then I should get the description concatenated
If just one of the substrings match, then I should get only the description for the ids that math
If there is no match, then BLANK

Customer ID	Product IDs	Description
AA_019442	123456A;123456B	AAAAAAAAAAA;BBBBBBBBBBBBB
BB_149432	123456A	AAAAAAAAAAA
CC_924742	123456F;123456C	CCCCCCCCCCCC
DD_127194	123456C	CCCCCCCCCCCC
EE_373878	123456E

I know that this could easily be accomplished with PowerQuery/M by splitting every product IDs by the delimiter ";" and matching them, but I'm asking for help with DAX because of how the PowerBI model has been made.

I thought at first the SEARCH function or the CONTAINSSTRING one would work, but I didn't manage to write the code for it, because I don't know how to search for contents in another unrelated table.

Thanks everyone in advance!!

Re: Search a substring in string from another table with DAX

barritown — Thu, 11 May 2023 09:18:59 GMT

Hi Anonymous,

You can try this solution:

However, you may not like some of its drawbacks:

- sorting in Products IDs may not match sorting in Description;

- if you have duplicates in Products IDs, they will have only one match in Description.

If those things don't bother you, it should be fine.

Here's the same calculated column in the text format:

Description = CONCATENATEX ( FILTER ( ADDCOLUMNS ( Table2, "Presence", CONTAINSSTRING ( [Products IDs], [Product ID] ) ), [Presence] = TRUE () ), [Description], ";" )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Search a substring in string from another table with DAX

MagnumUser — Fri, 28 Mar 2025 14:09:21 GMT

Barritown,

Thanks for this tip it is very usefull but I have a question. I have a situation where I want the result to be the sum of the the values rather than concatenate them. Is that possible?

Thanks