topic Re: How to detect the percentage of equal text in my column across rows in DAX Commands and Tips

How to detect the percentage of equal text in my column across rows

asofiacgraca — Thu, 11 May 2023 16:39:33 GMT

Hey, I am having some troubles here. Can someone help me, please?

I have some purchase orders and the description of each order. I am trying to figure out if there are purchase orders with like 80% match of their description.

Help, please 🙂

Re: How to detect the percentage of equal text in my column across rows

v-yueyunzh-msft — Mon, 15 May 2023 05:46:11 GMT

Hi , asofiacgraca

According to your description, you want to detect the percentage of equal text in my column across rows.

For your needs, in Power Query we mainly perform data transformation, if your text is a statement, then it may be difficult to directly use Power Query to calculate text similarity.

For comparing two separate words, we can use Text.ToList() to get a list of corresponding texts, then List.Intersect() to get the same string, and then use division to calculate text similarity.

For more information, you can refer to :
Solved: Compare two strings for similarity - Microsoft Power BI Community

For statement comparison, you can try to find a third-party API, and then use Web.Contents to call the API in Power Query to get the corresponding text similarity.

For more information, you can refer to :
Web.Contents - PowerQuery M | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: How to detect the percentage of equal text in my column across rows

tamerj1 — Mon, 15 May 2023 06:30:43 GMT

Hi asofiacgraca
Attached is an example of best match calcuation using DAX.

Re: How to detect the percentage of equal text in my column across rows

asofiacgraca — Mon, 15 May 2023 10:22:30 GMT

Hey tamerj1

Thank you for responding to my question. I am having some troubles tho. The table Standard is supposed to be created on VAR T3, right? I can't do that on my data, it only lets me use existing data on the first parameter. Can you help me?

Re: How to detect the percentage of equal text in my column across rows

asofiacgraca — Mon, 15 May 2023 10:24:02 GMT

Hey v-yueyunzh-msft

Thanks for your answer!
Yes, i am trying for the statement comparision 😕

Best regards 🙂

Re: How to detect the percentage of equal text in my column across rows

tamerj1 — Mon, 15 May 2023 10:30:42 GMT

asofiacgraca
Please provide some screenshots for better understanding.

Re: How to detect the percentage of equal text in my column across rows

asofiacgraca — Mon, 15 May 2023 10:38:52 GMT

Let's suppose I have this table with this data:

If I try to add your code in a calculated collumn on my table i have this error because the paremeter isnt the right type (i only have one table - table 1).

Re: How to detect the percentage of equal text in my column across rows

tamerj1 — Mon, 15 May 2023 10:43:01 GMT

asofiacgraca
Actually your requirement seems to be much simplier than best match pattern. Would you share some sample data or sample file to work with?

Re: How to detect the percentage of equal text in my column across rows

asofiacgraca — Mon, 15 May 2023 10:54:58 GMT

How can i attach a file here? having troubles doing so

Re: How to detect the percentage of equal text in my column across rows

tamerj1 — Mon, 15 May 2023 11:04:35 GMT

asofiacgraca
Please try this version. I guess it matches your requirement.

Dealer (Standard) = VAR String1 = Table1[Dealer] VAR Items1 = SUBSTITUTE ( String1, " ", "|" ) VAR Length1 = COALESCE ( PATHLENGTH ( Items1 ), 1 ) VAR T1 = GENERATESERIES ( 1, Length1, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items1, [Value] ) ) VAR T3 = ADDCOLUMNS ( VALUES ( Table1[Dealer] ), "@%Match", VAR String2 = Table1[Dealer] VAR Items2 = SUBSTITUTE ( String2, " ", "|" ) VAR Length2 = COALESCE ( PATHLENGTH ( Items2 ), 1 ) VAR T4 = GENERATESERIES ( 1, Length2, 1 ) VAR T5 = SELECTCOLUMNS ( T4, "@Item", PATHITEM ( Items2, [Value] ) ) VAR T6 = INTERSECT ( T5, T2 ) RETURN DIVIDE ( COUNTROWS ( T6 ), COUNTROWS ( T5 ) ) ) VAR T7 = FILTER ( T3, [@%Match] >= 0.8 ) RETURN COUNTROWS ( T7 )

Re: How to detect the percentage of equal text in my column across rows

asofiacgraca — Tue, 16 May 2023 08:03:01 GMT

Hey tamerj1

That seem to be working on my data set! Many thanks 😊

However, on my real data as it it a large set it's not running, do you know how to accelarate the process?

Thanks!

Re: How to detect the percentage of equal text in my column across rows

tamerj1 — Tue, 16 May 2023 08:13:25 GMT

asofiacgraca

Yes it is a pretty heavy calculation and won't work for big data. Not sure if can improved as DAX is not designed to perform such type of calculations.