Count by minute show blank minutes

[Drobinson1]

Trying to build a report that shows every minute of every day and count the occurances in another table if it falls on that spefic date and time. The scenario is we are trying to see if we achieve our packaging rate speeds.  We should be able to show 3 boxes per minute.  However out packaging team is only doing 1 or 2 per minute and frequently has minutes we no data caputered.  I would like to show their rates in green and the minutes they missed in red.

Right now i have a RC_Packaging_Rate table which has a record for each box along with a time stamp.  I also have another table called dates with every minute of everyday mapped out.

The two are not connected. I tried the formula below and couldn't get it to work.

counts = if(

ISBLANK(

CALCULATE(COUNT(RC_Packaging_Rate[CASE_COUNT]),FILTER(RC_Packaging_Rate,RC_Packaging_Rate[Hour]=Hour([getdate])&&RC_Packaging_Rate[minute]=MINUTE([getdate])&&RC_Packaging_Rate[Day]=DAY([getdate])))),0,

(CALCULATE(COUNT(RC_Packaging_Rate[CASE_COUNT]),FILTER(RC_Packaging_Rate,RC_Packaging_Rate[Hour]=Hour([getdate])&&RC_Packaging_Rate[minute]=MINUTE([getdate]&&RC_Packaging_Rate[Day]=DAY([getdate]))))))

[v-cherch-msft]

Hi @Drobinson1 

Sample data and expected output will be helpful to provide an accurate solution.If you need further help,please follow the How to Get Your Question Answered Quickly to post your data and expected output.It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

[Drobinson1]

I have a simple sensor sending data up to a table RC_Packaging_Rate and a disconnected table called dates(Just a simple one column excel import of every minute of every day for the next 3 years).

Everytime a case goes by on the line we have a timestamp.  We should be able to acheive 5 cases per minute.  In the green and red picture where there is red we have no counts.   

I then have a table with measures(again not jooins on any of the tables)

This has 

counts( which is the Green area on the chart) = CALCULATE(COUNT(RC_Packaging_Rate[CASE_COUNT]),FILTER(RC_Packaging_Rate,RC_Packaging_Rate[Hour]=Hour([getdate])&&RC_Packaging_Rate[minute]=MINUTE([getdate])&&RC_Packaging_Rate[Day]=DAY([getdate])))

 I want to show every minute of the day along with the counts and fill in teh blanks where there is no count.

And have this easily interact with a date slicer.

Right now my soultion requires me to drill up and then back down.

PACKAGING_ID	CASE_COUNT	TIME_STAMP	LINE	PLANT
27044	5	4/18/19 8:40 AM	NEW JUGGER	Ohio
27044	6	4/18/19 8:41 AM	NEW JUGGER	Ohio
27044	7	4/18/19 8:42 AM	NEW JUGGER	Ohio
27044	8	4/18/19 8:45 AM	NEW JUGGER	Ohio
27044	9	4/18/19 8:46 AM	NEW JUGGER	Ohio
27044	10	4/18/19 8:51 AM	NEW JUGGER	Ohio

[Drobinson1]

right now that red is a default measure i put in place that looks like this.  I don't really want to do this as every product on every line has a different value than the 3 that I am using in this example.

Missed counts2 = var counts=[counts]

return

if(ISBLANK(counts),3,0)