Forum Discussion
Create Customer table from Sale table with multiple Customer Names
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
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
14 Replies
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 👍
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,
KMTHow does the data look in the sales table for the blank customers, is it both the customer id and name that's blank?
