Create Customer table from Sale table with multiple Customer Names

[KyawMyoTun]

Dear Experts,

  I am trying to create a new unique "Customer" table from existing Sale table in dax.
Actully it can be created by using Summarize function with the reuqired columns.
The problem I am facing is some customers have changed their name over time.
So, the Summarize function can't handle the duplicate names.
The sale table is Direct Query Mode(Power Bi Dataset) and can't create duplicate tables in power query.
I'd like to get the Customer with lastest Customer Name.
The Sale Table

The expected Customer Table

Thanks a lot for your help.

Regards,
KMT

[DOLEARY85]

Hi,

try creating a new table with this, which works with your current data:

New Table =

ADDCOLUMNS(

DISTINCT('Table'[CUSTOMER ID]),

"Customer Name", CALCULATE(MAX('Table'[CUSTOMER NAME]), ALLEXCEPT('Table', 'Table'[CUSTOMER ID])))

Alternatively try:

New Table =

ADDCOLUMNS(

DISTINCT('Table'[CUSTOMER ID]),

"Customer Name", CALCULATE(FIRSTNONBLANK('Table'[CUSTOMER NAME],CALCULATE(MAX('Table'[ORDER ID])))))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[KyawMyoTun]

Dear @DOLEARY85 ,

  Thanks a lot for your help and it is working properly.
I'd like to request you another one becuase there have some blank customers in sale table.
I'd like to remove those blank customers before creating customer table.
Can you please help for this also? Thanks.

Regards,
KMT

[DOLEARY85]

How does the data look in the sales table for the blank customers, is it both the customer id and name that's blank?

[KyawMyoTun]

Yes, both customer id and customer names are the blank
becuase of some manual invoicing.
I'd like to ignore the customers for those invoices. Thanks.

[DOLEARY85]

Sorry i think i may have misread what you wanted, so if you wanted another version of the table with those 5 fields but without blank customers you could summarize a new table:

Table 2 = FILTER(SUMMARIZE('Table','Table'[CUSTOMER ID],'Table'[CUSTOMER NAME],'Table'[ORDER DATE],'Table'[ORDER ID],'Table'[SALE AMOUNT]),not('Table'[CUSTOMER ID]=""))

which changes:

to:

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[KyawMyoTun]

Dear @DOLEARY85 ,

Actually I only want two columns with Customer ID and Custoumer Name
Where both are not blanks.
The below code are nearly working properly but some customers still getting old name.

FILTER(ADDCOLUMNS(

DISTINCT('Table'[CUSTOMER ID]),

"Customer Name", CALCULATE(FIRSTNONBLANK('Table'[CUSTOMER NAME],CALCULATE(MAX('Table'[ORDER ID]))))),NOT('Table'[CUSTOMER ID]=""))

I think it is better to filter the max Order Date than Order ID because
some reversal sale comes by larger order ID. Thanks.
Please help again.

[DOLEARY85]

Okay, it's an easy change then, just change Order ID to Order Date and it'll work the same way:

New Table =

FILTER(ADDCOLUMNS(

DISTINCT('Table'[CUSTOMER ID]),

"Customer Name", CALCULATE(FIRSTNONBLANK('Table'[CUSTOMER NAME],CALCULATE(MAX('Table'[ORDER DATE]))))),NOT('Table'[CUSTOMER ID]=""))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[KyawMyoTun]

I've test that one but still some customers getting old name.

[DOLEARY85]

Could you share a couple of the records with the old and new names so i can test why they're not changing. Or could you share you PBIX file?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[KyawMyoTun]

Thanks a lot for your help.
I'll share the pbix soon and it might delay some time for other urget matter.
Thanks a lot for your help again.

Regards,
KMT

[DOLEARY85]

Okay try chaning the new table formula to(i've added in bold the additions to a previous table:

New Table =

FILTER(ADDCOLUMNS(

DISTINCT('Table'[CUSTOMER ID]),

"Customer Name", CALCULATE(FIRSTNONBLANK('Table'[CUSTOMER NAME],CALCULATE(MAX('Table'[ORDER ID]))))),NOT('Table'[CUSTOMER ID]=""))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[KyawMyoTun · Accepted solution]

Dear @DOLEARY85 ,
I have tried and some old names still because of FIRSTNONBLANK function I think.
So, I tried with LASTDATE function of Order date and it gave me the expected results.
Pbix file is not able to share for me.
So, Please check the screenshot and let me know your thought.
Thanks a lot for your help.

Best Regards,
KMT

[DOLEARY85]

Hi,

yeah that looks good, FIRSTNONBLANK should have worked too due to it being filtered by the max order date. Without seeing the PBIX file i'm not sure why that didn't work but if adapting my solution with LASTDATE gives you what you need then that's great. Glad it worked.

happy to help 🙂

[KyawMyoTun]

Dear @DOLEARY85 ,

  The expected result is coming of adapting your solution.

Thanks for helping. Happy learning.

Regards,
KMT