cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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.

 

ShiftConfig.PNG

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

 

Sensor Events.PNG

 

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

 

Relationship model.PNG

 

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

2 REPLIES 2
kentyler New Contributor
New Contributor

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

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

Highlighted
M_L Frequent Visitor
Frequent Visitor

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

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 : 

image.png

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)