cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luukvv93 Regular Visitor
Regular Visitor

Need 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])

tempsni2p.png

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:

tempsnip.png

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!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Need help with difficult measure

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)

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Re: Need help with difficult measure

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7

Re: Need help with difficult measure

@Luukvv93 can you share sample data in excel file to work with it. Here is a post on how to get your answer quickly.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Luukvv93 Regular Visitor
Regular Visitor

Re: Need help with difficult measure

Luukvv93 Regular Visitor
Regular Visitor

Re: Need help with difficult measure

All help is greatly appreciated
Luukvv93 Regular Visitor
Regular Visitor

Re: Need help with difficult measure

Meanwhile this has not been solved, could anyone give it a go?

Re: Need help with difficult measure

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)

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Luukvv93 Regular Visitor
Regular Visitor

Re: Need help with difficult measure

@TomMartens 

 

Amazing Tom, it works. Could you explain me the logic behind generateseries and treatas?

Re: Need help with difficult measure

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,312)