Hello,

As part of analyzing our fire department's response times, we measure the time it takes to get a certain amount of people on a fire scene. I have two table that contains incident data. The incident table contains one row per incident. The apparatus table contains multiple rows per incident with each row contain the information for each unit. They are connected by the incident key with a one to many relationship. 

The column apparatus.num_personnel contains the count of personnel for each fire truck. The goal is to capture the response time of the unit that is carrying the 10th person to the scene. In the example below, E78 was the unit that the sum of apparatus.num_personnel was => 10. There response time of 0:06:18 is returned to the incident table. 

I realize I will need to create a logical test (calculated column on the incident table) that sums the number of personnel for each incident and then when that number => 10, then return the MIN Response Time of that row. From past projects, I understand I can create a temporary table (ADD COLUMNS) which will iterate over each incident row, comparing the units for that incident. I"m just not sure how to put it all together. 

Any help or push in the right direction, is greatly appreciated. 

Brent

PS. Overview of data model. The incident and apparatus are my data tables and the rest are lookup tables. The parameter table is used for a percentile measure, but doesn't play a role with this project. 

You might try doing this by adding a Calculated Column to each table.

First, on the apparatus table, add a column to do the "test" on the running total for an incident:

apparatus[Over10]=
CALCULATE(
     SUM(apparatus[num_personnel]),
     FILTER(
          ALLEXCEPT(apparatus, apparatus[Incident Key]),
          apparatus[Response Time]  <= EARLIER(apparatus[Response Time])
     )
) >= 10

Then create a column in incident that iterates thru the TRUE() values in apparatus[Over10] to find the minimum response time with 10 or more people:

incident[10 Person Response Time]=
MINX(
     CALCULATETABLE(
          apparatus, 
          apparatus[Over10] = TRUE()
    ), 
    apparatus[Response Time]
)

Hi,

I tried to run the formula, but I received a memory failure #error. I am only running 32 bit at home, but I will try this at work tomorrow on my 64 bit application. In the mean time, I will step through the formulas. 

Thank your for the response and I will post back tomorrow. 

Let me know how you make out on 64 bit. Which column gave you the error and how many rows do your tables have?

You might try doing this by adding a Calculated Column to each table.

First, on the apparatus table, add a column to do the "test" on the running total for an incident:

apparatus[Over10]=
CALCULATE(
     SUM(apparatus[num_personnel]),
     FILTER(
          ALLEXCEPT(apparatus, apparatus[Incident Key]),
          apparatus[Response Time]  <= EARLIER(apparatus[Response Time])
     )
) >= 10

Then create a column in incident that iterates thru the TRUE() values in apparatus[Over10] to find the minimum response time with 10 or more people:

incident[10 Person Response Time]=
MINX(
     CALCULATETABLE(
          apparatus, 
          apparatus[Over10] = TRUE()
    ), 
    apparatus[Response Time]
)

• Marked as answer by bvanscoy678 16 hours 7 minutes ago

It was on the Apparatus column and my model has 1 month of data, which is about 10,000 rows in the apparatus table 5,000 rows in incident. 

I had an issue earlier this week running another calculated column, which is not normal for me. Oddly enough, it was an MINX with nested Earlier function. 

I will try to cut back the number of rows to just a few hundred. 

Thanks

Other issue this week:

MINX (
    FILTER (
        VALUES ( SDE_Units[TURNOUT] ),
        CALCULATE (
            COUNTROWS ( SDE_Units ),
            SDE_Units[TURNOUT] <= EARLIER ( SDE_Units[TURNOUT] )
        )
            > COUNTROWS ( SDE_Units ) * 0.9
    ),
    SDE_Units[TURNOUT]
)

10,000 isn't much data even for 32 bit. One thing you can try to improve memory issues is remove any columns that you don't need for the analysis. One thing that sticks out to me is the key in th apparatus table. It doesn't look like it is needed in the model for relationships or anything so it might be an unnecessary, high cardinality column which eats up a lot or memory in Power Pivot. Even with that, I'd be surprised that 10,000 rows is an issue. Try it on the 64 bit machine first though because that should breeze thru 10,000 rows.

Hi,

I was able to limit the incidents to just last week for a lower number the calculated columns worked. I am going to step through a few incidents to get a better idea. 

The apparatus table contains the data for each unit and the number of personnel and contains your first calculated column. 

One thing I can do is just limit my initial query to only fire calls. That will drop the number dramatically. I should have thought about that first. 

I would be more than happy to place file in drop box if you send me an email. bvanscoy678@gmail.com

Thanks for the help and I'll post back after a bit. 

Brent

Your formula is working as expected!

I am going to do a little work with my data before I import (SQL view) it to improve performance on my machine. I am using a few calculated columns to create my response time, which I can easily do in SQL.  I am working with Fires, so the number of incidents is dramatically less than EMS calls. If I constraint the amount of data coming into the model, I shouldn't have any issues. With that being  said, I have much more complex models in the past with no issues. I'll need to follow up with our IT support to see if I have any underlying RAM issues. 

Our offices are closed in remembrance for 911 (I came in to work on this project), so I'll have to tackle the rest on Monday. 

I'll mark the post as complete, but I will post back next week with a follow up. Thanks for taking the time to work on my post. 

Thanks,

Brent

Below is a screen shot of one incident (one row incident table) and the units in the apparatus table. *way to small to see. I can send  the workbook if you like. 

Glad you were able to get it working.

I'll be interested to hear how you make out next week after optimizing your model and checking your computer configurations.

Have a good weekend!

Thanks!