Calculate Unrelated Table with Slicer

[Anonymous]

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])
RETURN 
IF(CountDateRows,SumTCARD/(DATEDIFF(MINX(ScheduleTitle,tbl_Schedule[StartDate]),MAXX(ScheduleTitle,tbl_Schedule[DueDate]),DAY)-CountNonDateRows),BLANK())

And here is a couple screenshots:

[v-jiascu-msft]

Hi @Anonymous,

1. Where is [Calc TCARD Baseline]? It seems a Calculated Column. If so, I'm afraid it won't respond to the slicer. Can you apply it as a measure?

2. The Slicer isn't from the Calendar table. So the relationship won't be a problem. 

Can you share a dummy sample that has several rows to show the structure?

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft -

[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.

SampleData

[v-jiascu-msft]

Hi @Anonymous,

I rewrite it as a measure. Please download the demo from the attachment.

Calc TCARD Baseline Measure =
VAR ScheduleTitle =
    FILTER ( tbl_Schedule, tbl_Schedule[Title] = "Preliminary Landowner Research" )
VAR CountDateRows =
    MIN ( 'tbl_Calendar'[Date] ) >= MINX ( ScheduleTitle, tbl_Schedule[StartDate] )
        && MIN ( 'tbl_Calendar'[Date] ) <= MAXX ( ScheduleTitle, tbl_Schedule[DueDate] )
        && MIN ( tbl_Calendar[Calc Working Day] ) = 1
VAR CountNonDateRows =
    COUNTROWS (
        FILTER (
            ALL ( tbl_Calendar ),
            [Date] >= MINX ( ScheduleTitle, tbl_Schedule[StartDate] )
                && [Date] <= MAXX ( ScheduleTitle, tbl_Schedule[DueDate] )
                && tbl_Calendar[Calc Working Day] = 0
        )
    )
VAR SumTCARD =
    SUM ( tbl_LineList[Calc TCARDREQD] )
RETURN
    IF (
        CountDateRows,
        SumTCARD
            / (
                DATEDIFF (
                    MINX ( ScheduleTitle, tbl_Schedule[StartDate] ),
                    MAXX ( ScheduleTitle, tbl_Schedule[DueDate] ),
                    DAY
                )
                    - CountNonDateRows
            ),
        BLANK ()
    )

Cumulative =
CALCULATE (
    SUMX ( tbl_Calendar, [Calc TCARD Baseline Measure] ),
    FILTER (
        ALL ( tbl_Calendar[Date] ),
        'tbl_Calendar'[Date] <= MAX ( 'tbl_Calendar'[Date] )
    )
)

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft

Wait - I just noticed - the column in the matrix is showing the total as being the average.  The Cumulative column subtotal row and the per-day subtotal should both be the same.  It's a SUM not an AVG.  😞

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

That won't be a problem. Try this formula, please. If it works, please mark the proper answers as solutions.

Calc TCARD Baseline Measure =
VAR ScheduleTitle =
    FILTER ( tbl_Schedule, tbl_Schedule[Title] = "Preliminary Landowner Research" )
VAR CountDateRows =
    MIN ( 'tbl_Calendar'[Date] ) >= MINX ( ScheduleTitle, tbl_Schedule[StartDate] )
        && MIN ( 'tbl_Calendar'[Date] ) <= MAXX ( ScheduleTitle, tbl_Schedule[DueDate] )
        && MIN ( tbl_Calendar[Calc Working Day] ) = 1
VAR CountNonDateRows =
    COUNTROWS (
        FILTER (
            ALL ( tbl_Calendar ),
            [Date] >= MINX ( ScheduleTitle, tbl_Schedule[StartDate] )
                && [Date] <= MAXX ( ScheduleTitle, tbl_Schedule[DueDate] )
                && tbl_Calendar[Calc Working Day] = 0
        )
    )
VAR SumTCARD =
    SUM ( tbl_LineList[Calc TCARDREQD] )
RETURN
    SUMX (
        tbl_Calendar,
        IF (
            CountDateRows,
            SumTCARD
                / (
                    DATEDIFF (
                        MINX ( ScheduleTitle, tbl_Schedule[StartDate] ),
                        MAXX ( ScheduleTitle, tbl_Schedule[DueDate] ),
                        DAY
                    )
                        - CountNonDateRows
                ),
            BLANK ()
        )
    )

Best Regards,
Dale

[Anonymous]

So a few hiccups along the way, getting this to work in my file, but by-golly you did it.  Thanks!  Now to disect your formula and understand the difference!