Forum Discussion

andersbq's avatar
andersbq
Frequent Visitor
5 years ago
Solved

Create summarized table

Hello all, 

 

I got two tables with order data (ecom/retail).  My goal is to create a summarized table (based on the email address) that looks like Table 3. Table 1 & 2 has a lot of columns, below are the matching ones. 

 

TABLE 1    
EmailNameOrder IDProduct IDOrder date
[email protected]John Doe121210102020-01-01
[email protected]John Doe121212122020-01-01
[email protected]Jane Doe121512022020-05-05
[email protected]Jane Doe121512072020-05-05

 

TABLE 2    
EmailNameOrder IDProduct IDOrder date
[email protected]John Doe202010102020-04-01
[email protected]John Doe202012122020-04-01
[email protected]Jane Doe204012022020-06-05
[email protected]Jane Doe204012032020-06-05

 

TABLE 3   
EmailNameFirst orderLatest order
[email protected]John Doe2020-01-012020-04-01
[email protected]Jane Doe2020-05-052020-06-05

 

Would really appreciate your help on this one!

 

Thanks!

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    andersbq , refer if this can help

    https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

     

    Try like

     

    summarize(
union (
selectcolumns(Table1,"Email",Table1[Email] ,"Name",Table1[Name],"Order ID",Table1[Order ID],"Product ID",Table1[Product ID],"Order date",Table1[Order date]),
selectcolumns(Table2,"Email	Name",Table2[Email] ,"Name",Table2[Name],"Order ID",Table2[Order ID],"Product ID",Table2[Product ID],"Order date",Table2[Order date])
), [Email],[Name],"First order" ,min([Order date]),"Latest order",max([Order date]))
    • andersbq's avatar
      andersbq
      Frequent Visitor
      5 years ago

      Thanks! amitchandak - but I get this error message: Cannot identify the table that contains [Order date] column.

    • andersbq's avatar
      andersbq
      Frequent Visitor
      5 years ago

      Awesome, works like a charm!

       

      Thanks! Anonymous