New table with parent-child hierarchy and date

[Anonymous]

I've found a couple of leads here and elsewhere by google already but haven't been able to solve the issue: I have two date tables, the first one lists the market demand quantity & need date; while the second table lists the process and time needed. The idea is to measure the time needed for a process during a month. Prior to Power BI, I was running excel Macro with for-loop to get the same result (new table), then using Pivot Table to visualize the insight. Wonder if Power BI can make this task quick and simple.

Example below:

Table A - Demand

Date	Product	Quantity (Demand)
2020/4/1	A	10
2020/4/1	B	20
2020/5/1	B	30

Table B - Process

Product	Process	Time Needed (Minutes)
A	Cut	20
A	Oven	20
A	Inspect	10
B	Cut	25
B	Inspect	15

And here's the desired outcome:

Date	Product	Process	Total Time*
2020/4/1	A	Cut	200
2020/4/1	A	Oven	200
2020/4/1	A	Inspect	100
2020/4/1	B	Cut	500
2020/4/1	B	Inspect	300
2020/5/1	B	Cut	750
2020/5/1	B	Inspect	450

*Total time= (Time needed of a process/product) * (Demand during a month )

[amitchandak · Accepted solution]

@Anonymous 

Try like

summarize(
filter(
crossjoin(selectcolumns(Demand,"Date",Demand[Date],"Product",Demand[Product],"Quantity (Demand)",Demand[Quantity (Demand)])
, selectcolumns(Process,"Bproduct",Process[Product],"Process",Process[Process],"Time Needed (Minutes)",process[Time Needed (Minutes)])
),[Product] = [Bproduct]),[Date],[Product],[Process], "Total Time",sum([Quantity (Demand)]*[Time Needed (Minutes)]))

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could try to implement it in Query Editor. Please follow this to have a try.

[Anonymous]

@v-xuding-msft  it works perfectly well. I appreciate for your help!

[amitchandak · Accepted solution]

@Anonymous 

Try like

summarize(
filter(
crossjoin(selectcolumns(Demand,"Date",Demand[Date],"Product",Demand[Product],"Quantity (Demand)",Demand[Quantity (Demand)])
, selectcolumns(Process,"Bproduct",Process[Product],"Process",Process[Process],"Time Needed (Minutes)",process[Time Needed (Minutes)])
),[Product] = [Bproduct]),[Date],[Product],[Process], "Total Time",sum([Quantity (Demand)]*[Time Needed (Minutes)]))

[Anonymous]

@amitchandak it almost works except the sum function shows "Parameter is not the correct type." I use "new column" to resolve the issue and managed to get the correct multiply though.

Thanks for your quick answer!