Create a new table

[FRG]

Hi,

I want to create a new table to summurize 2 existing tables. Table1

And the other (not linked with the first) is detail about products, Table2:

In a new table I want 3 columns:

- The IdProduction (Table1) when idProcess = 2, 

- The count of ID (Table2) when ScaledOn is between TimeShiftBegin and TimeShiftEnd (Table1),

- The sum of volumeDisplay (Table2) when ScaledOn is between TimeShiftBegin and TimeShiftEnd (Table1).

Is there a way to do that with Power BI?

Thanks

[v-diye-msft · Accepted solution]

Hi @,

I’ve created a sample similar with yours: there’re table 1 and table 2

I use below formulas to generate table 3 and table 4 under your condition:

Table 3 = CALCULATETABLE('Table1',FILTER(Table1,[idProcess]=2))
Table 4 = CALCULATETABLE(Table2,FILTER(Table2,[ScaledOn]>MAX(Table1[TimeShiftBegin])&&[ScaledOn]<MAX(Table1[TimeShiftEnd])))

There’s additional new column added in table 4 for sum of volumeDisplay:

sum of vd = SUM('Table 4'[volumeDisplay])

Then use the crossjoin function to create the final table:

Table = CROSSJOIN(SELECTCOLUMNS('Table 3',"idpro",[IdProduction]),SELECTCOLUMNS('Table 4',"idcount",[ID],"sum of vd",[sum of vd]))

attached pbix here: 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EV50Bj2EiAdLn9nt09Kt1R...

Best regards,

Dina Ye

[v-diye-msft]

Hi @FRG ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye