Forum Discussion

KyawMyoTun's avatar
KyawMyoTun
Icon for Helper IV rankHelper IV
3 years ago
Solved

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 ...
Need Help
  • KyawMyoTun's avatar
    KyawMyoTun
    3 years ago

    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's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
KyawMyoTun
Icon for Helper IV rankHelper IV
3 years ago

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's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      KyawMyoTun
      Icon for Helper IV rankHelper IV
      3 years ago

      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's avatar
        DOLEARY85
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        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 🙂

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      KyawMyoTun
      Icon for Helper IV rankHelper IV
      3 years ago

      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's avatar
        DOLEARY85
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        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 👍