new table from connected existing tables

[prakritnepal]

Hi, 

I am trying to create a new temp table based on the selection. This is supposed to show the expected stages in order to ship process and the current stage an order is currently at. The part I am not being able to do is bring data together from different tables and put it in a gantt chart based on the selection on the slicer. Gantt chart is kinda important here because that gives a clear view of expected ship dates and completion dates for different labor processes. The current structure of my data is something like:

Table1
Order number	order date
1234	1/1/2019
4567	1/1/2019
5678	1/1/2019

Table 2
order number	job num
1234	qwer
4567	asdf
5678	zxcv

Table 3
Job num	lab connector
qwer	1
asdf	2
zxcv	3

Table 4
lab connector	op process	start date	end date
1	item 1	2/1/2019	2/11/2019
1	item 2	2/11/2019	2/21/2019
1	item 3	2/21/2019	3/3/2019

Table 5
Job num	Eng date	doc date
qwer	2/12/2019	2/11/2019
asdf	2/13/2019	2/21/2019
zxcv	2/14/2019	2/27/2019

Desired Output based on slicer selection:

selection 1234
Items	Start date	End date
Order Date	1/1/2019
Eng date		2/12/2019
Doc date		2/11/2019
item 1	2/1/2019	2/11/2019
item 2	2/11/2019	2/21/2019
item 3	2/21/2019	3/3/2019

The data is coming from different sources and there are layers of connecting tables. I can get all the data individually but I am not being able to put it together in a gantt chart. 

What I have tried:

merging/appending: too much data and no clear way to filter that. couldn't figure out a way

Union-select column:

Temp table = UNION(SELECTCOLUMNS('Erp JobOper',"Item",'Erp JobOper'[OpCode],"complete",'Erp JobOper'[OpComplete],"start date",'Erp JobOper'[QueStartDate],"due date",'Erp JobOper'[DueDate],"num",'Erp JobOper'[JobNum]),

SELECTCOLUMNS('Erp OrderHed',"Item",'Erp OrderHed'[OrderNum],"complete",'Erp OrderHed'[OpenOrder],"start date",'Erp OrderHed'[OrderDate],"due date",'Erp OrderHed'[DoNotShipAfterDate],"num",'Erp OrderHed'[OrderNum]))

this kinda did what I was looking for but again no clear way filter this table as I need to filter by order number and still get all the data. 

ideally, if I can do union-select for data from related tables based on the slicer selection that would be great. I would love to have other options as well if possible or a better way.

[v-cherch-msft]

Hi @prakritnepal 

You may create table 6 and use below measure.Attached sample file for your reference.

Start date =
IF (
    MAX ( Table6[Items] ) = "Order date",
    CALCULATE ( MAX ( Table1[order date] ) ),
    IF (
        MAX ( Table6[Items] ) = "item 1",
        CALCULATE (
            MAX ( Table4[start date] ),
            FILTER ( ALLSELECTED ( Table4 ), Table4[op process] = "item 1" )
        ),
        IF (
            MAX ( Table6[Items] ) = "item 2",
            CALCULATE (
                MAX ( Table4[start date] ),
                FILTER ( ALLSELECTED ( Table4 ), Table4[op process] = "item 2" )
            ),
            IF (
                MAX ( Table6[Items] ) = "item 3",
                CALCULATE (
                    MAX ( Table4[start date] ),
                    FILTER ( ALLSELECTED ( Table4 ), Table4[op process] = "item 3" )
                )
            )
        )
    )
)

Regards,

[prakritnepal]

Thank you for the suggestion. 

Is there a way the items can be pulled in dynamically? They change from one order to another for the type of operation. 

[v-cherch-msft · Accepted solution]

Hi @prakritnepal 

You may try 'Unpivot columns' in query editor and Union function to get the table.For example.

Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "item", Table1[Attribute] ),
        SELECTCOLUMNS ( Table4, "item", Table4[op process] ),
        SELECTCOLUMNS ( Table5, "item", Table5[Attribute] ),
        SELECTCOLUMNS ( Table5, "item", Table5[Attribute.1] )
    )
)

Regards,