Upcoming Dates from Multiple Tables

[ReadTheIron]

Good morning! I'm trying to create a list of items based on dates from multiple tables. I have two tables that look like this:

I'm trying to create a table that shows the upcoming work and looks like this:

I've tried creating a calendar table and relating its Date column to Inspection, Repair, and Completed in both tables, but when I try to write a calculated column it won't even let me reference the Switch or Circuit tables. I was thinking something like Upcoming Inspection =IF('Switches'[Inspection] > TODAY(), 'Switches'[Inspection], BLANK()), but this wouldn't work for trying to combine the two tables. Any guidance appreciated!

[jppv20 · Accepted solution]

Hi @ReadTheIron ,

I think you got 2 options here.

1) Append both tables in the Query editor, so it will become one table.

2) Create a third table containing all the items to create a relationship between these tables.

If I answered your question, please mark it as a solution to help other members find it more quickly.

[ReadTheIron]

Thanks! This got me on the right track - I ended up creating a third table and then adding a column EarliestOfEither which took the earliest date from any of the columns, and using that as a filter. @v-angzheng-msft , your solution looks more elegant; I'm still playing around with it. Much appreciated.

[v-angzheng-msft · Accepted solution]

Hi, @ReadTheIron 

@jppv20  mentioned two good solutions above.

@ReadTheIron It should be noted that if you append two tables, the column names in the tables must be the same. You need to make the first column of the two tables the same, such as items.

The second solution above seems to be perfect, but I doubt that it is complicated to get the dates of two tables at the same time in one measure. @ReadTheIron If I were you, I would suggest to create a calculation table, first filter the two tables and then summarize the filtered tables to get a new table.

Result:

Items = 
var _t1=SELECTCOLUMNS(FILTER('Switches','Switches'[Inspection]>TODAY()),"Items",[Switches],"Upcoming inspection",[Inspection],"Repair",[Repair])
var _t2=SELECTCOLUMNS(FILTER('Circuits',[Inspection]>TODAY()),"Items",[Circuits],"Upcoming inspection",[Inspection],"Repair",[Repair])

return UNION(_t1,_t2)

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jppv20 · Accepted solution]

Hi @ReadTheIron ,

I think you got 2 options here.

1) Append both tables in the Query editor, so it will become one table.

2) Create a third table containing all the items to create a relationship between these tables.

If I answered your question, please mark it as a solution to help other members find it more quickly.