Forum Discussion
Helper IINeed help with difficult measure
I need your help with a difficult measure.
I have a dataset containing sensor data of the amount of crates (AantalOmverpakkingen) that pass a sensor over time (conveyor belt). The data is grouped date and hours. Example:
AantalOmverpakkingen = SUM(FustPrestatiePerUur[AantalOmverpakkingen])
Secondly I have a dataset containing the working hours of the people (Grouped by date, shift, starttime, endtime).
Example of the table and the virtual table:
ShiftTabel =
SUMMARIZE(FactUren;
FactUren[Datum];
FactUren[Shift];
"BeginTijd"; MIN(FactUren[BeginTijd]);
"EindTijd"; MAX(FactUren[EindTijd])
)I need your help to come up with a measure that sums the crates (AantalOmverpakkingen) per shift based on shifttimes.
The result should look like this:
Date, Shift, ShiftStart, ShiftEind, AantalOmverpakkingen
26-3-2019, Dag, 7:00:00, 14:15:00, AantalOmverpakkingen
26-3-2019; Avond; 14:30:00, 22:30:00, AantalOmverpakkingen
All help is appreciated!
Hey,
i use the following DAX to create a column in your shift table - not a measure - but I think this will get you started:AantalOmverpakkingen = var fractionOfTheDayInMinutes = 1 / (24 * 60) var thisShiftStart = 'Shift'[ShiftStart] var thisShiftEnd = 'Shift'[ShiftEind] return CALCULATE( SUM(Sensor[AantalOmverpakkingen]) ,TREATAS( GENERATESERIES(thisShiftStart, thisShiftEnd + fractionOfTheDayInMinutes, fractionOfTheDayInMinutes) ,Sensor[DatumUur] ) )The report will look like this (just a table visual)
Hopefully this is what you are looking for.
Regards,
Tom
Hey Luukvv93 ,
first GENERATESERIES(start, end, increment)
GENERATESERIES creates a one-column table, where the column is called value. It adds the increment (in our example the fraction that represents a minute) until the end is reached.
As there is no column called value in your table called sensor, I use TREATAS to map the content of the table, to the column(s) in the sensor table, to filter all the rows where the date column from the sensor table is in the table created by the GENERATESERIES function. TREATAS allows to treat columns inside the table (the 1st parameter), as columns from the "outer table". Filtering is based on the concept of data lineage, for this reason TREATAS can become quite handy :-)
Please mark the most helpful post as answer as this might also help others.
Regards,
Tom
7 Replies
Luukvv93Meanwhile this has not been solved, could anyone give it a go?
- TomMartens
Super User
Hey,
i use the following DAX to create a column in your shift table - not a measure - but I think this will get you started:AantalOmverpakkingen = var fractionOfTheDayInMinutes = 1 / (24 * 60) var thisShiftStart = 'Shift'[ShiftStart] var thisShiftEnd = 'Shift'[ShiftEind] return CALCULATE( SUM(Sensor[AantalOmverpakkingen]) ,TREATAS( GENERATESERIES(thisShiftStart, thisShiftEnd + fractionOfTheDayInMinutes, fractionOfTheDayInMinutes) ,Sensor[DatumUur] ) )The report will look like this (just a table visual)
Hopefully this is what you are looking for.
Regards,
Tom
- Luukvv93
- TomMartens
Hey Luukvv93 ,
first GENERATESERIES(start, end, increment)
GENERATESERIES creates a one-column table, where the column is called value. It adds the increment (in our example the fraction that represents a minute) until the end is reached.
As there is no column called value in your table called sensor, I use TREATAS to map the content of the table, to the column(s) in the sensor table, to filter all the rows where the date column from the sensor table is in the table created by the GENERATESERIES function. TREATAS allows to treat columns inside the table (the 1st parameter), as columns from the "outer table". Filtering is based on the concept of data lineage, for this reason TREATAS can become quite handy :-)
Please mark the most helpful post as answer as this might also help others.
Regards,
Tom
