Forum Discussion

koorosh's avatar
koorosh
Post Partisan
4 years ago

table expression

Hi, I expected Customer and Purchase Date header for the following output. why I got Sales_Customer and Sales_Purchase Date?    
Need Help
koorosh's avatar
koorosh
Post Partisan
4 years ago

Hi, Since the sales table has "customer" and "purchase date" columns, so the Lastpurchase variable that is the outcome of GroupBY should have common join columns to get correct output from NATURALLEFTJOIN as you see in the following!

 

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    Hi koorosh ,

     

    Could you pls provide some dummy data with expected output for test?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

    • koorosh's avatar
      koorosh
      Post Partisan
      4 years ago

      Hi Kelly, I tried to break the final code in “Days since Last Purchase” into many steps. I expected in “Purchase step2” step, I got a table with “customer”, “Purchase date” and “Last purchase” columns. But as you see they are “sales_customer”, “sale_purchse date” and “Last purchase” instead.

      https://drive.google.com/file/d/1Ily0fzWIu4MZTbfDQa-DfCrqyrc6m_fC/view?usp=sharing

       

      Best regards

      Koorosh

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        4 years ago

        Hi  koorosh ,

         

        Should be something related to GENERATE Function,but I cant tell why.Maybe because that you have renamed the column names during generating.

        But if you wanna get "Purchase_step2" with the headers you need,I have a simpler way:

        First create a column in Sales as below:

        Last Purchase Date =
CALCULATE (
    MAX ( 'sales'[Purchase Date] ),
    FILTER (
        'sales',
        'sales'[Customer] = EARLIER ( sales[Customer] )
            && 'sales'[Purchase Date] < EARLIER ( sales[Purchase Date] )
    )
)

        Then create table "Purchase_step2" as following:

        _Purchase_step2 =
VAR _tab =
    SELECTCOLUMNS (
        'sales',
        "Customer", [Customer],
        "Purchase Date", 'sales'[Purchase Date],
        "Last Purchase", 'sales'[Last Purchase Date]
    )
RETURN
    FILTER ( _tab, [Last Purchase] <> BLANK () )

        And you will see:

         

        Check the .pbix attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!