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
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!

     

    • koorosh's avatar
      koorosh
      Post Partisan
      4 years ago

      Hi Kelly, I am not sure that you noticed my point or not. I am saying in the final code in “Days since last purchase”, NATURALLEFTOUTERJOIN distinguished that Sales table and LastPrchase table (created by GROUPBY) have columns with identical header names (Customer column and Purchase Date column) , so that the output has customer and Purchase Date columns.

      But when I cut the last part of the code (cut NATURALLEFTOUTERJOIN) as you see in the “Purchase_step2” table , the output has not columns with header Customer and Purchase Date name. I did not change anything in GROUPBY function but the out put has columns  “sales_customer” and “sales_purchase date” not “Customer” and “Purchase Date” columns.

       

      Best Regards

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

        Hi  koorosh ,

         

        I know,if I comment out part of your expression,I get the troubling result as you say:

        Based on my experience,if you use a table as a variable to get another table,it will be affected by context.

        As we cant to query the internal mechanism of the function,I cant tell whether it is a bug,that's why I show you another method to get the result you need.

         

         

        Best Regards,
        Kelly

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