Help aggregating data from indirect table

[jpoitras]

New to Power BI
Scraped the forums but haven't landed on an answer that worked for me.

I have 3 tables

 - WorkStream: just an id

 - WorkStreamCapacity: workStreamId, User, Capacity (number in hours)

 - Tasks: WorkStreamId, User, Estimate (number in hours)

Capacity is related to Work stream as *:1 since a Work Stream can have multiple resources
Tasks are related to WorkStreams *:1
Capacity and Tasks are not related through users as it would be *:*

I'm just looking to show planned utlization per user for a work stream in a single component. Ideally with a slicer on the page to allow people to chose the workstream they care about. Ex: 14 hours planned of 30 hours available

I only get the proper values from 1 side of the data (starting either from tasks or capacity). Ex: if I put capacity by user on a bar chart I get the correct result. Adding in estimate of tasks will just give me the total sum of all tasks for that work stream, which makes sense since the filtering is for that stream.

I feel like there is a way to use a measure here potentially on the capacity side that calculates the SUMX fo the task estimates per user / itteration but I'm not getting it to work.

[jpoitras · Accepted solution]

So I've solved my problem by normalizing some of my data further, but mainly by creating a measure using crossfilter which amounts to:
In my WorkStreamCapacities table
Allocation = CALCULATE(SUM(Tasks[Estimate]), CROSSFILTER(Tasks[WorkStreamId],WorkStream[id], both))

Now I can show a person's estimated allocation against their available capacity sliced by work stream.

[Greg_Deckler]

Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[jpoitras]

workItems.pbix

Thanks @Greg_Deckler,

I've included a link to a pbix with some dummy data that hopefully highlights the scenario.
At a high level, I effective just want to be able to display the amount of capacity a person has in a given work stream against the total hour of tasks assigned to them for that work stream.

Ideally users can use a slicer to target the work stream they care about.

The main issue Im facing is that total task time and total capacity are indirectly related to one another through the work stream table.

I believe I should be able to lean on measures here, but rearanging the data might also be appropritate. Just looking for some guidance.

In my example file Im also running into an issue where the slicer on the common workStream table doesn't seem to be affecting the tasks table, but not sure whats going on there. This answer https://community.powerbi.com/t5/Desktop/One-slicer-for-two-tables-M-M/td-p/523095 from someone else seems to be the exact relationship I have so I would expect the total task estimates to reflect this slice, but it always seems to display as the grand total.