table expression

[koorosh]

Hi, I expected Customer and Purchase Date header for the following output. why I got Sales_Customer and Sales_Purchase Date?

[koorosh]

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]

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]

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]

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]

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]

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!

[mwegener]

Hi @koorosh ,

It seems that the DAX function GROUP BY puts the table name as prefix in front of the corresponding GROUP BY columns.