Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Hi,
try creating a new table with this, which works with your current data:
Alternatively try:
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,
KMT
How does the data look in the sales table for the blank customers, is it both the customer id and name that's blank?
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.
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:
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
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.
Okay, it's an easy change then, just change Order ID to Order Date and it'll work the same way:
I've test that one but still some customers getting old name.
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 👍
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
Okay try chaning the new table formula to(i've added in bold the additions to a previous table:
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
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 🙂
Dear @DOLEARY85 ,
The expected result is coming of adapting your solution.
Thanks for helping. Happy learning.
Regards,
KMT