Filter two dimensions across two tables for a line and column chart

[jim842]

Hi,

I'm hitting some of the limits of my PBI knowledge so i'm not even sure if what i'm asking for is the right terminology. 

I have two tables once that lists a persons capacity by day across a month, the other that lists the work that is assigned to them in a given month (there are some other columns but not relevant to this action)

Work Capacity Table

SprintStartDate	UserName	UserNameHour	DayType	WorkDate
12/1/2022	Person A	6	WorkDay	12/1/2022
12/1/2022	Person A	6	WorkDay	12/2/2022
12/1/2022	Person A	6	WorkDay	12/5/2022
12/1/2022	Person A	6	WorkDay	12/6/2022
12/1/2022	Person B	6	WorkDay	12/1/2022
12/1/2022	Person B	6	WorkDay	12/2/2022
12/1/2022	Person B	6	WorkDay	12/5/2022
12/1/2022	Person B	6	WorkDay	12/6/2022
12/1/2022	Person C	6	WorkDay	12/1/2022
12/1/2022	Person C	6	WorkDay	12/2/2022
12/1/2022	Person C	6	WorkDay	12/5/2022
12/1/2022	Person C	6	WorkDay	12/6/2022
12/1/2022	Person D	6	WorkDay	12/1/2022
12/1/2022	Person D	6	WorkDay	12/2/2022
12/1/2022	Person D	6	WorkDay	12/5/2022
12/1/2022	Person D	6	WorkDay	12/6/2022

Work Activity Table

Assigned To	Iteration Start Date	Iteration End Date	Title	State	Work Item Type	Remaining Work
Person C	12/1/2022	12/31/2022	Data	Done	Task	0
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	1
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	6
Person C	12/1/2022	12/31/2022	Data	Not Started	Task	24
Person C	12/1/2022	12/31/2022	Data	Not Started	Task	4
Person C	12/1/2022	12/31/2022	Data	Not Started	Task	8
Person C	12/1/2022	12/31/2022	Data	Not Started	Task	8
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	18
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	5
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	8
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	2
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	2
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	5
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	5
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	2
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	6
Person B	12/1/2022	12/31/2022	Data	Not Started	Task	1

Right now i have a Many > Many Relationship Setup between the tables between

Username [in Capacities] and Assigned to [in Work Activity]

So right now if i select a user, It will filter to only that user on both tables.  

From there i'm able to make seperate views that will filter my results.  However if i try to add both together, all that ends up happening is that my remaining work ends up just being a sum and not actually filtered to the right month for each user

The same thing happens in reverse if i try to look at it on my visual for remaining work

I tried making two many:many relationships, but it doesn't look like i can have them both active.

Ideally i'd want the months to line up on each graph, but i'm not sure how to tackle that. 

I'm assuming I need to create some sort of intermediary table (or possibly a table that summarizes the remaining capacity per person per month) to be able to sort on multiple dimensions like this, but I'm not sure of what I need to even be looking for.  

Any help that can point me in the right direction would be super appreciated

[jim842]

Awesome!

Thank you!

[amitchandak · Accepted solution]

@jim842 , You need to create a common employee, date, and user name table and analyze data across that 

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...