topic Classify data based on another table (fuzzy match or CONTAINSSTRING) in DAX Commands and Tips

Classify data based on another table (fuzzy match or CONTAINSSTRING)

amani1980 — Sun, 03 Nov 2024 17:24:54 GMT

Hi,

I'm trying to group tickets into product category based on comparing one table to another. The match on ticket type from one table to another isn't exact. It's more like if the ticket string string in Table 1(AA0765AA) contains a part of the one in Table 2 (765), then that will be classified as Bananas. It's not an exact comparison. The output table will look like this.

I suppose CONTAINSSTRING will work. However I can't seem to get the syntax right. I'm trying group Table 1 into Type and do some analysis. Can someone help me with this please? Model here.

Thanks in advance,

Re: Classify data based on another table (fuzzy match or CONTAINSSTRING)

FreemanZ — Sun, 03 Nov 2024 18:17:32 GMT

hi amani1980 ,

try to extract the numbers from the string

1) in Power Query, how:

https://monocroft.com/extract-numbers-from-a-string-in-power-bi/

2) If you insist to do it with DAX code and supposing the dataset is as uniform as we see in the sample, you can try to write a calculated column with DAX like:

ticket=

VALUE(MID(table1[Ticket No], 4, 4))

then try to join with table2 and use RELATED to get the type column.

Re: Classify data based on another table (fuzzy match or CONTAINSSTRING)

Omid_Motamedise — Mon, 04 Nov 2024 03:08:36 GMT

Use Power Query and in the merge command check the fuzzy merging as highlighted below