cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ReadTheIron
Helper III
Helper III

Upcoming Dates from Multiple Tables

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:

ReadTheIron_0-1638978462651.png

ReadTheIron_1-1638978501156.png

 

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

 

ReadTheIron_2-1638978542864.png

 

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!

 

2 ACCEPTED SOLUTIONS
jppv20
Solution Sage
Solution Sage

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.

View solution in original post

v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1639363474938.png

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.

View solution in original post

3 REPLIES 3
ReadTheIron
Helper III
Helper III

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
Community Support
Community Support

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:

vangzhengmsft_0-1639363474938.png

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.