DAX Generate statement to self join a table

Question

Hi,&nbsp;I'm trying to join a date table to itself using a &gt;= operator.&nbsp; The date table contains Period End Dates, and contains only a single column, which is the DateKey in integer format (ex: 20190131).&nbsp; The combined date table should contain two columns.&nbsp; The first column is the original DateKey, and the second column is each DateKey that is &gt;= the original date key.&nbsp; Ex: Let's say the table contains three Period End Dates: 20190131, 20181231, and 20181130.&nbsp; The combined date table would look like this:&nbsp;DateKey1&nbsp; &nbsp; &nbsp;DateKey220190131&nbsp; &nbsp; &nbsp;2019013120190131&nbsp; &nbsp; &nbsp;2018123120190131&nbsp; &nbsp; &nbsp;2018113020181231&nbsp; &nbsp; &nbsp;2018123120181231&nbsp; &nbsp; &nbsp;2018113020181130&nbsp; &nbsp; &nbsp;20181130&nbsp;If they were two separate date tables, I could use a GENERATE statement to achieve the result.&nbsp; This works:&nbsp;GENERATE(DateTable1, SUMMARIZE(FILTER(DateTable2,DateTable2[DateKey] &lt;= DateTable1[DateKey]),DateTable2[DateKey]))&nbsp;However, if I just reference DateTable1, I get an error that the GENERATE does not allow two columns with the same name:&nbsp;GENERATE(DateTable1, SUMMARIZE(FILTER(DateTable1,DateTable1[DateKey] &lt;= DateTable1[DateKey]),DateTable1[DateKey]))&nbsp;&nbsp;I'm looking for suggestions about how to code this statement without needing two versions of the table.&nbsp;Thanks for any help.&nbsp;

liviolanzo · Accepted Answer

Hello&nbsp;Anonymous&nbsp;try this:&nbsp;Table = 
GENERATE(
    Data,
    SELECTCOLUMNS(
        CALCULATETABLE(
            VALUES( Data[DateKey] ),
            Data[DateKey] &gt;= EARLIER( Data[DateKey] )
        ),
        "DateKey2", Data[DateKey]
    )
)

Forum Discussion

DAX Generate statement to self join a table

2 Replies

Related content

Lakehouse Table Generate Create Table

Table UPDATE statements on the same Warehouse table.

Generating Missing Data

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

BULK INSERT statement is generally available!