Connect multiple columns in two tables

[sfink22]

I have two tables that pull project information. The first table is pulled from our project database and includes all info with deadlines for all of the project steps, the project owner, etc.

The second table has calculated columns that count how many times a certain project step is due for a certain period across all projects. So each project step column looks to the left of the timeline table (which has two years of period + year), then scans the matching project step column in the project info table and counts how many times that period + year appears. This is perfect for displaying a timeline that shows how many projects are due for each project step across the years (i.e. distribution across time of work). 

However, I can't connect the multiple project step columns to the project info table. It will only let me connect one column at a time, so my graphs are not dynamic in the report. I have 4 different project steps that I'd like to show on the timeline that can change dynamically when, for example, a specific project owner is chosen.

Here's an example of my project info table:

Project Name	Owner	Step 1 Due Date	Step 2 Due Date	Step 3 Due Date	Step 4 Due Date
ABC	Jack	2020P06	2020P07	2020P08	2020P10
DEF	Sally	2020P01	2020P02	2020P06	2020P08
GHI	Bob	2020P02	2020P05	2020P06	2020P010
JKL	Sally	2020P03	2020P06	2020P07	2020P08
MNO	Bob	2020P01	2020P05	2020P06	2020P09

Here's an example of my timeline table ("calculated" from data above):

Timeline	Step 1 Count	Step 2 Count	Step 3 Count	Step 4 Count
2020P06	1	1	3
2020P07		1	1
2020P08		1		2

Not every date shown in timeline appears in the project info table. Right now I can only create one active relationship at a time with the Timeline column and one Step Due Date column, not all four Step Due Date columns like I need. This means that charts using the Timeline table info don't change when I select a project name or an owner.

[v-zhenbw-msft]

Hi @sfink22 ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft]

Hi @sfink22 ,

We can use the unpivot function in Power Query Editor to meet your requirement.

1. We can select [Project Name] and [Owner] in Power Query Editor.

2. Then use the unpivot other columns,

We can get a table like this,

4. And we need to create a relationship between two tables based on Timeline.

5. And we can create a matrix table on report page. Put the [Timeline] to Rows, put the [Attribute] to Columns, put the [value] to values and count it.

We need to configure the [Timeline] is not blank.

6. At last we can create two slicers to control the matrix table.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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