Returning all results from one table defined by a date range in another table

[chrissembroski]

I have two tables. One is a table of historical data that includes rows of building data. For example,

RoomTemperature, 72.1, timestamp, Room24

RoomTemperature, 72.0, timestamp, Room24

RoomTemperature, 72.1, timestamp, Room24

RoomTemperature, 72.2, timestamp, Room24

CO2Levels, 401, timestamp, Room24

CO2Levels, 410, timestamp, Room24

RoomTemperature, 75.3, timestamp, Room24

RoomTemperature, 75.4, timestamp, Room24

CO2Levels, 455, timestamp, Room24

CO2Levels, 449, timestamp, Room24

RoomTemperature, 76.1, timestamp, Room24

RoomTemperature, 73.0, timestamp, Room24

RoomTemperature, 72.1, timestamp, Room24

The second table contains alarm data. For example:

101, "High Room Temperature Alarm", starttime, endtime, Room24

102, "High CO2 Levels Alarm", starttime, endtime, Room24

The goal is to filter by a single alarm incident, and have a visual return all the historical values within the starttime and endtime date/time range. For example, if I select alarm incident 101, I want to see a line graph with the RoomTemperature and CO2Levels data values that are only within the starttime and endtime of the alarm incident 101.

So far, I have been able to link the two tables by a unique equipmentID using a third table where Room24 is only listed once. I have been able create a calendar table so that I can link a single unique time to all data at that time. However, I can't get a visual to filter between a dynamic date range; the filter on the visual/page/report doesn't allow me to use columns--only before, after, or on a predefined date.

Any ideas?

Thank you!

[v-caliao-msft · Accepted solution]

@chrissembroski,

You can use your data in table 1 in your visual, and then add a filter into it. Before add filter, you need to create a measure in table1.

Measure =
var selectedID = MAX(Table2[ID])
var selectedType = LOOKUPVALUE(Table2[Type];Table2[Type];selectedID)
var selectedRoom = LOOKUPVALUE(Table2[Room];Table2[Type];selectedID)
var starttime = LOOKUPVALUE(Table2[Starttime];Table2[Type];selectedID)
var endtime = LOOKUPVALUE(Table2[Endtime];Table2[Type];selectedID)
return IF(MAX(Table1[Room])=selectedRoom&&MAX(Table1[Type])=selectedType&&MAX(Table1[DateTime])>=starttime&&MAX(Table1[DateTime])<=endtime;1;0)

If this is not what you want, please provide us some sample data so that we can make further analysis.

Regards,

Charlie Liao

[chrissembroski]

Thanks @v-caliao-msft! Still learning the in's and out's of DAX. One comment is that I needed to change all the ";" to "," but otherwise the general solution works.