First, I can't share my data as it is contractually protected. So hopefully y'all can help without it.
I have three tables I'm working with:
tbl_Schedule is a MS Project schedule shoved into SharePoint as a list for a data source. tbl_Calendar is a calculated table which lists all the dates from tbl_Schedule (MIN being earliest Start Date, MAX being latest Due Date). tbl_LineList is an Oracle table that has a bunch of tasks (columns) and flags as to whether a task is required (REQD).
I am using a calculated column in tbl_LineList called "Calc TCARDREQD" which says, if TCARDREQD = "Yes", then 1.
I am trying to sum up all my "Calc TCARDRED".
The TCARD work is divided among two subcontractors based on locations (which are listed per row in tbl_LineList) using "Calc ROW Subcon".
Right now, I have a "baseline" calculation that sums up all the TCARDREQD across the whole project. But I have no way to relate the Calendar to the LineList, so when I add a slicer, it has no impact on my SUM of CalcTCARDREQD. Here is the formula:
Calc TCARD Baseline = VAR ScheduleTitle = FILTER(tbl_Schedule,tbl_Schedule[Title]="Preliminary Landowner Research")
VAR CountDateRows = [Date]>=MINX(ScheduleTitle,tbl_Schedule[StartDate]) && [Date]<=MAXX(ScheduleTitle,tbl_Schedule[DueDate]) && tbl_Calendar[Calc Working Day]=TRUE()
VAR CountNonDateRows = COUNTROWS(FILTER(tbl_Calendar,[Date]>=MINX(ScheduleTitle,tbl_Schedule[StartDate]) && [Date]<=MAXX(ScheduleTitle,tbl_Schedule[DueDate]) && tbl_Calendar[Calc Working Day]=FALSE()))
VAR SumTCARD = SUM(tbl_LineList[Calc TCARDREQD])
[Calc TCARD Baseline] lives in the Calendar table. I can't put it in the LineList table (the fact table) because I have no idea when each row will be completed (establish a baseline) so it's better for the calculation to exist next to the dates and just say "If there are 2300 rows in the fact table, divide that equally by X number of days", which is what [Calc TCARD Baseline] does.
The relationship is the problem. If I had a relationship between the fact table and the calendar table, then it would slice just fine. But there isn't any columns that could relate the two UNLESS I used the TCARDCOMPLETEDATE column, but that won't work because there's like 10 tasks all summarized in the fact table that would need to relate separately with the calendar table.
Also, I tried to make it a measure, but then it wouldn't evaluate Max/Min dates and such.
I'm attaching some severely trimmed down data. The Calendar didn't have anything sensetive specifically in it, so that one is mostly left untouched. The Linelist I have trimmed down to only include the columns that the data is pulled from. The Schedule has been trimmed to remove rows except the one task we're filtering on to figure out start/end dates. Hopefully this will help.