Calculating the max temperature based on date range & location

[staceyeileen]

Hello folks,

I am new to PowerPivot and having some difficulty with a measure I am trying to create. I am dealing with a very large data set (hence, why I turned to PP) that involves temperature readings of various locations in a manufacturing facility. Temperature readings are captured every 15 minutes via temperature probes in various locations. Here is the simplified model...

Locations table:
Name ProbeNumber
Loc1 17
Loc2 17
Loc3 20
Loc4 31

Probes table:
ProbeNumber Name
17 ProbeA
20 ProbeB
31 ProbeC

Readings table:
Timestamp ProbeNumber Reading
12/15/12 08:00 17 -25.33
12/15/12 08:10 20 -33.15
12/15/12 08:15 17 -27.50
12/15/12 08:15 31 -39.00
12/15/12 08:25 20 -32.25
etc, etc (millions of rows)


Finally, I have a table that tracks the movement of products throughout the facility as it moves in and out of various locations, named Movement.

Serial# Location TimeIn TimeOut
123 Loc4 12/10/12 08:08 12/16/12 13:40
123 Loc1 12/16/12 13:40 12/15/12 22:45
456 Loc2 12/12/12 12:35 12/15/12 08:22

I want to calculate the max temp in the Readings table that corresponds to a given row in the Movement table. I had already added a calculated column for MaxTemp in the Movement table that seems to be working using MAXX, but this was before I discovered what a Measure is and the fact that you can only create KPIs off measures. I'm not sure how to adapt my calculated column formula to create a MaxTemp measure:

=MAXX(FILTER(Readings,
Readings[Timestamp] >=[TimeIn] &&
Readings[Timestamp] <= [TimeOut] &&
Readings[ProbeNumber] = RELATED(Locations[ProbeNumber])) ,
Readings[Reading])

Everything I've tried using CALCULATE and MAX has thrown an error about not being able to determine the context of a field. Your help is greatly appreciated!

 

[Laurent C]

First a quick note: Are you sure about the model you present? From your Locations table, it seems that one probe might come from different locations, which seems to contradict your requirements.
You first used a calculated column wich returned the desired result for each entry in the movement table. How did you use (or intend to use) the data in this calculated column? In other words, how would you aggregate these MAX temperatures?

 

[staceyeileen]

First a quick note: Are you sure about the model you present? From your Locations table, it seems that one probe might come from different locations, which seems to contradict your requirements.
You first used a calculated column wich returned the desired result for each entry in the movement table. How did you use (or intend to use) the data in this calculated column? In other words, how would you aggregate these MAX temperatures?

Yes, one probe may monitor many locations. I know it sounds off, but it's due to the granularity of the locations. Each location is actually a specific shelf inside of a freezer. So in my sample data above, all readings for Probe 17 are applicable for both Loc1 and Loc2.

 

[staceyeileen]

You first used a calculated column wich returned the desired result for each entry in the movement table. How did you use (or intend to use) the data in this calculated column? In other words, how would you aggregate these MAX temperatures?

I have a list of specific serial numbers that I need to analyze to determine the ones that have continuously been stored within acceptable temperature limits - so, all max temps are below a designated value. Then for the ones where any of the max temp readings associated with that serial number falls above acceptable limits, I'll probably be doing some further analysis to determine duration it was stored above acceptable limits, etc.

 

[Laurent C]

Ok. This makes much more sense.
To address a specific value in a column, then you will need a row context. A measure does not have one at the root level. To obtain one, you can use one of the iterator functions (SUMX, COUNTX, ...)
For a global Max Temp measure, you could do the following, for example:
[Max Temp] :=
MAXX( Movement,
MAXX(
FILTER(
Readings,
Readings[Timestamp] >= Movement[TimeIn] &&
Readings[Timestamp] <= Movement[TimeOut] &&
Readings[ProbeNumber] = RELATED(Locations[ProbeNumber])) ,
Readings[Reading]
)
)
(Untested)
However, this might not be the most efficient way to write the calculation.
Since you already have a calculated column with the max temp for each movement row, then you could also create measures based on this column (and then create KPIs out these).
Keep in mind that since calculated columns are stored in the model, they have the advantage of not requiring an additional calculation at query time (even if they have the disadvantage of using memory both for storage and at run-time).
Given your requirements, you might want to keep your calculated column.