Binning data by time problem

[cme263]

Dynamic Segmentation and Dynamic Range … problem for the timedimension.<o></o>
I have a pile of log data from a variety of sources from adatabase. There are multiple column of identifiers, followed by a few columnsof various data, with the last column being a complete year to second Time Stamp.All data is only reported every 5 minutes, so all data is reported on the hourand every 5 minutes of the hour at 00 seconds. The next 4 columns, Date, Time,Hour and Min are derived. I am looking for the DAX formula (see below forframework that is not working!) that will create the selectable Time Resolutionreporting of various calculated functions on other data columns. The last 2columns are internal examples of the expected data per the selected timeresolution. <o></o>
Let’s call thistable “Report”<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD]ID1<o></o>
[/TD]
[TD]ID2<o></o>
[/TD]
[TD]ID3<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]…<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Data1<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Data2<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]…<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]Time Stamp<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]Date<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]Time<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Hour<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]Minute<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

Data1_for _10min_ resolution<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

Data1_for _15min_ resolution<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 42, bgcolor: transparent"]East<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Lab3<o></o>
[/TD]
[TD="width: 36, bgcolor: transparent"]X<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]3<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]300<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]10/28/2016 10:00<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]10/28/2016<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10:00<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

4<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

6<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 42, bgcolor: transparent"]East<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Lab3<o></o>
[/TD]
[TD="width: 36, bgcolor: transparent"]X<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]1<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]300<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]10/28/2016 10:05<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]10/28/2016<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10:05<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]5<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 42, bgcolor: transparent"]East<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Lab3<o></o>
[/TD]
[TD="width: 36, bgcolor: transparent"]X<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]2<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]300<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]10/28/2016 10:10<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]10/28/2016<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10:10<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 42, bgcolor: transparent"]East<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Lab3<o></o>
[/TD]
[TD="width: 36, bgcolor: transparent"]X<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]300<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]10/28/2016 10:15<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]10/28/2016<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10:15<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

0<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 42, bgcolor: transparent"]East<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Lab3<o></o>
[/TD]
[TD="width: 36, bgcolor: transparent"]X<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]300<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]10/28/2016 10:20<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]10/28/2016<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10:20<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]20<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

0<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 42, bgcolor: transparent"]East<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]Lab3<o></o>
[/TD]
[TD="width: 36, bgcolor: transparent"]X<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]300<o></o>
[/TD]
[TD="width: 24, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 132, bgcolor: transparent"]10/28/2016 10:25<o></o>
[/TD]
[TD="width: 96, bgcolor: transparent"]10/28/2016<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]10:25<o></o>
[/TD]
[TD="width: 48, bgcolor: transparent"]10<o></o>
[/TD]
[TD="width: 60, bgcolor: transparent"]25<o></o>
[/TD]
[TD="width: 77, bgcolor: transparent"]

<o></o>​

[/TD]
[TD="width: 116, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
I have created a table “res_bin”. This table is my sourcethe resolution time and time binning ranges within the hour (for the Minute). <o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD]index<o></o>
[/TD]
[TD]resol<o></o>
[/TD]
[TD]time<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]low<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]high<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]1<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]60<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]60<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]2<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]3<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]60<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]4<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]0<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]5<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]6<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]30<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]45<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]7<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]15<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]45<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]45<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]60<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 57, bgcolor: transparent"]…<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[TD="width: 57, bgcolor: transparent"]<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
Again, I am trying to group Data by binning the reporting Timeby a resolution time. I get an error, as soon as I try to reference the res_bin[resol].I am clearly having a filter issue. What am I not getting? Thanks in advance.
Data1SumbyTimeResolution:=<o></o>
IF (<o></o>
ISFILTERED ( res_bin[resol]),<o></o>
IF (<o></o>
HASONEVALUE(res_bin[resol] ),<o></o>
CALCULATE (<o></o>
[Sum(Data1)],<o></o>
FILTER(<o></o>
VALUES ( Report[Data1] ),<o></o>
[Minute]>= VALUES ( res_bin[low] ) &&[Minute] < VALUES ( res_bin[high] )<o></o>
)<o></o>
),<o></o>
BLANK ()<o></o>
),<o></o>
[Sum(Data1)]<o></o>
)<o></o>
<o></o>
<o></o>
<o></o>