table expression

Question

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

mwegener · Answer

Hi&nbsp;koorosh&nbsp;,
&nbsp;
It seems that the DAX function GROUP BY puts the table name as prefix in front of the corresponding GROUP BY columns.
&nbsp;
&nbsp;

koorosh · Answer

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 · Answer

Hi&nbsp;koorosh&nbsp;,
&nbsp;
Could you pls provide some dummy data with expected output for test?
&nbsp;
Best Regards,Kelly
Did I answer your question? Mark my reply as a solution!

koorosh · Answer

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 · Answer

Hi&nbsp;&nbsp;koorosh&nbsp;,
&nbsp;
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] )
            &amp;&amp; 'sales'[Purchase Date] &lt; EARLIER ( sales[Purchase Date] )
    )
)

Then create table&nbsp;"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] &lt;&gt; BLANK () )

And you will see:

&nbsp;
Check the .pbix attached.
&nbsp;
Best Regards,Kelly
Did I answer your question? Mark my reply as a solution!
&nbsp;

Forum Discussion

table expression

7 Replies

Related content

Help!!! Expression Error

Regular expression

Query (1, 1) The expression specified in the query is not a valid table expression

table filter expression in CALCULATE() function

Expression Error