Calculate sum of values in one table based on time range defined in another table

[Anonymous]

Dear all,

I have a problem. I want to calculate the sum of a value in another table based on a defined time frame.

In this table I have specified the time range and the column output shall retrieve the sum of "digital Value" in table sensor_events for machine 2.

Table sensor events looks like this (there are several entries falling in a certain time range).

I have set relations between machine ID and start and end time. Looks like this:

The table machine contains just the master data for machine, and is used as a report filter. Maybe I do not need that one?

Thanks for helping me out on this!

Best,

Cornelius

[M_L]

Hello,

If you want to use the relationship, I would create a concatenated key in both tables with the concatenation of machine id, start and end date, then create the relationship. The sum will then work in sensor_events table. 

Example of key with a new column in sensor_events table 

sensor_events_KEY = FORMAT(sensor_events[machine_id];"General Number")&"^"& FORMAT(sensor_events[time_start];"General Number") &"^"& FORMAT(sensor_events[time_stop];"General Number")

 Example of key with a new column in Shift_Config table 

Shift_Config_KEY = FORMAT(Shift_Config[MachineId];"General Number")&"^"& FORMAT(Shift_Config[Start_Time];"General Number") &"^"& FORMAT(Shift_Config[End_Time];"General Number")

then create the relationship, and the calculate should work. 

Output = CALCULATE(SUM(sensor_events[digital_value]))

output : 

[kentyler]

It looks like shift config and sensor events are both acting like fact tables. Actually they look like they fall into the "header/detail" pattern, with shift config being the header.

Try merging them together...so every sensor event record also has its shift config data. The shift config data will repeat... but it will make calculation much easier. For one thing, you will be rid of those 3 relationships that otherwise you would always be having to activate in code...
You could probably also use a calendar table... to link to the shift date/times so you can filter reports by day or week or month. 
In order to combine the tables you will need to add the values from shift config to all the sensor events whose time_start is betwen shift config's start time and end time.
this will configure you data in a "star" and the values you want to combine will no longer be in different tables.. i believe just putting them in a matrix will do the job...no need to write dax. See more on star schemas at www.allstarsbi.com