Search a substring in string from another table with DAX

[Anonymous]

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!!

[barritown · Accepted solution]

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

[MagnumUser]

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