cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pikamatt Regular Visitor
Regular Visitor

Calculate Unrelated Table with Slicer

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:
image.pngimage.png
1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Calculate Unrelated Table with Slicer

Hi @pikamatt,

 

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 ()
        )
    )

Calculate-Unrelated-Table-with-Slicer2

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
v-jiascu-msft Super Contributor
Super Contributor

Re: Calculate Unrelated Table with Slicer

Hi @pikamatt,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pikamatt Regular Visitor
Regular Visitor

Re: Calculate Unrelated Table with Slicer

@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 Super Contributor
Super Contributor

Re: Calculate Unrelated Table with Slicer

Hi @pikamatt,

 

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] )
    )
)

Calculate-Unrelated-Table-with-Slicer

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pikamatt Regular Visitor
Regular Visitor

Re: Calculate Unrelated Table with Slicer

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!

pikamatt Regular Visitor
Regular Visitor

Re: Calculate Unrelated Table with Slicer

@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.  Smiley Sad

v-jiascu-msft Super Contributor
Super Contributor

Re: Calculate Unrelated Table with Slicer

Hi @pikamatt,

 

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 ()
        )
    )

Calculate-Unrelated-Table-with-Slicer2

 

 

Best Regards,
Dale

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 21 members 840 guests
Please welcome our newest community members: