Forum Discussion

Phil_P's avatar
Phil_P
Regular Visitor
8 years ago
Solved

Generating A New Table

Hi,

 

I am very new to Power BI and need some assistance. I have two tables (with no related columns) and want to generate a third from the two. The idea is to generate a weekly statistics table (each Sunday) for my key transactions.

 

So my input tables are:

 

Table 1

Date Table 
DateDOW
01/01/2018 00:002
02/01/2018 00:003
03/01/2018 00:004
04/01/2018 00:005
05/01/2018 00:006
06/01/2018 00:007
07/01/2018 00:001
08/01/2018 00:002
09/01/2018 00:003
10/01/2018 00:004
11/01/2018 00:005
12/01/2018 00:006
13/01/2018 00:007
14/01/2018 00:001
15/01/2018 00:002

 

Table 2

Key Transaction   
ApplicationKey TransactionTimeLink
App1Trans 13ABC
App1Trans 23BCD
App1Trans 32CDE
App2Trans 41DEF

 

And what I am trying to create is:

Result    
DateApplicationKey TransactionTimeLink
07/01/2018 00:00App1Trans 13ABC
07/01/2018 00:00App1Trans 23BCD
07/01/2018 00:00App1Trans 32CDE
07/01/2018 00:00App2Trans 41DEF
14/01/2018 00:00App1Trans 13ABC
14/01/2018 00:00App1Trans 23BCD
14/01/2018 00:00App1Trans 32CDE
14/01/2018 00:00App2Trans 41DEF

 

Can anyone assist?

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    Phil_P,

     

    You may use DAX below to add a calculated table.

    Table =
CROSSJOIN (
    Table2,
    SELECTCOLUMNS ( FILTER ( Table1, Table1[DOW] = 1 ), "Date", Table1[Date] )
)

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    Phil_P,

     

    You may use DAX below to add a calculated table.

    Table =
CROSSJOIN (
    Table2,
    SELECTCOLUMNS ( FILTER ( Table1, Table1[DOW] = 1 ), "Date", Table1[Date] )
)
    • Phil_P's avatar
      Phil_P
      Regular Visitor
      8 years ago

      That works brilliantly - Thanks