Fuzzy measure in dax not in power query

[KyawMyoTun]

Dear Experts,
 I am looking for a help for my issue with consolidated customer sales as below.
Sale (fact table)

Customer ID	Customer Name	Invoice no	Sale Amount	Invoice Date
1001	Flower Co.,Ltd	100001	100	1/2/2024
1002	Flower Company Limited	100002	200	1/3/2024
1003	Xray Inc.Ltd	100003	100	1/3/2024
1003	Xray Inc	100004	500	1/4/2024

In this sale table, the Customer ID 1001 and 1002 are the same customer but wrongly opened as new customer.
And then, the Customer ID 1003 has two different names but the latest one is the correct one.
So, I created a new table by Customer Name as below to extract by the correct customer name and sale amount.

Customer Name

Flower Co.,Ltd

Xray Inc

Although fuzzy matching in power query is working properly, it takes a lot of time consuming.
So, I'd like to ask you a help to do it by dax as the below final table.

Customer Name	Sale Amount	Invoice Date	Fuzzy Customer Name
Flower Co.,Ltd	100	1/2/2024	Flower Co.,Ltd
Flower Co.,Ltd	200	1/3/2024	Flower Company Limited
Xray Inc	100	1/3/2024	Xray Inc.Ltd
Xray Inc	500	1/4/2024	Xray Inc

Thanks, and I am highly appripriate for your help.

Regards,
KMT

[v-weiyan1-msft]

Hi @KyawMyoTun ,

Based on the sample and description you provided, Please try the following steps:
1. You can create a new table as follows.

There is a one-to-one relationship between the two tables.

2. Use the following code to create a Calcualted table.

Table 3 = 
NATURALINNERJOIN (
    NATURALINNERJOIN ( 'Table 2', VALUES ( 'Table'[Sale AmountInvoice] ) ),
    VALUES ( 'Table'[Date] )
)

Result is as below.

Best Regards,
Yulia Yan

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

[KyawMyoTun]

Dear @v-weiyan1-msft ,

  Thanks a lot for your reply and help.
But the sale table contains multiple transactions of each customer(But I am sorry that I only put less transactions). The sale table should be like below.

Customer Name	Sale Amount	Invoice Date	Fuzzy Customer Name
Flower Co.,Ltd	100	1/2/2024	Flower Co.,Ltd
Flower Co.,Ltd	200	1/3/2024	Flower Company Limited
Xray Inc	100	1/3/2024	Xray Inc.Ltd
Xray Inc	500	1/4/2024	Xray Inc
Xray Inc	400	1/5/2024	Xray Inc
Flower Co.,Ltd	300	1/5/2024	Flower Co.,Ltd

So, we can't make 1 to 1 relationship because sale table has multiple transctions for individual customer.
Please take a look into this and let me know if you find some alternative way on this.

Regards,
KMT

[lbendlin]

Have two reference tables

First, for ID mapping

Customer ID	Ultimate Customer ID
1001	1001
1002	1001

And another on for names  

Customer Name	Ultimate Customer Name
Flower Co.,Ltd	Flower Company Limited
Xray Inc.Ltd	Xray Inc.

Or combine them into a single reference table.  That reference table should reside outside of Power BI, for example an Excel file on a SharePoint. That way you clean up the data without having to modify the report.

[KyawMyoTun]

Dear @lbendlin,
Thanks a lot for your help and reply.
The mapping should be mostly based on the customer name actually.
I am not so clear on your answer and can you please explain a litter more details. Thanks.

Regards,
KMT