dynamic binning, 2 layer bin table

[cme263]

I have this working in standard excel.. but as I add some additional functionality.. bad news with MAX needing the “Ctrl+Shift+Enter” for the array. Must move to DAX and Power Pivot.

I want to be able to take a data log of timed attributes, and do 2 things. I need to bin the data by selectable time resolutions (res_bin[resol]), and chart it against the clean equivalent resolution time elements.


REALLY struggling with DAX filters…. confused by Values, SelectedAll, or CrossFilter.

I have what should be great examples online… but something is not working for me.
Let’s call this table “Data”

Region	Site	Attribute	Time Stamp	Date	Time	Hour	Min
East	Lab_3	2	10/28/2016 10:00	10/28/2016	10:00	10	0
East	Lab_3	2	10/28/2016 10:05	10/28/2016	10:05	10	5
East	Lab_3	2	10/28/2016 10:10	10/28/2016	10:10	10	10
East	Lab_3	2	10/28/2016 10:15	10/28/2016	10:15	10	15

<tbody>
</tbody>

(assorted attribute data, 24 hour a day every 5 minutes from multiple Regions and Sites)
The first 4 columns are from a imported file, and the following 4 helper columns are derived:
Date = (date(year([Time Stamp]),month([Time Stamp]),day([Time Stamp]))
Time = format(hour([Time Stamp]),"00")&":"&format(minute([Time Stamp]),"00")
Hour = hour([Time Stamp])
Min = minute([Time Stamp])

I have created a table “res_bin”. This table is my source the accumulated data binning as defined by the minutes in time, and the associated time (minutes) ranges with the hour.
[TABLE="width: 320"]
<tbody>[TR]
[TD]index
[/TD]
[TD]resol
[/TD]
[TD]time
[/TD]
[TD]low
[/TD]
[TD]high
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]60
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]15
[/TD]
[TD]00
[/TD]
[TD]0
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD]15
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]15
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]15
[/TD]
[TD]45
[/TD]
[TD]45
[/TD]
[TD]60
[/TD]
[/TR]
</tbody>[/TABLE]

My simple totals, average, and max are fine, and when graphed at the (default) 5 minute resolution, all is well.
There are a fact a number of ‘Attibute’s, and pivot tables in motions, so slicers are in play for chart management. A Slicer for “Resolution Time” [resol] is to be the controlling element. Keep getting an error… help.

res_bin_SUM:=if(
ISFILTERED(res_bin[resol]),
If(
HASONEVALUE(res_bin[resol]),
CALCULATE(SUM([ATTRIBUTE]),
FILTER(res_bin, value([min])>=values([low]) && value([min])<values([high]))),
BLANK()
),
SUM([ATTRIBUTE])
)

{in the above, ‘SUM’ could be replaced with MAX, AVERAGE, MIN or DIV(attribute1/attribute2) for a weighted average, etc.
The charted pivot tables would have the legend (series) of Region, Site, with the axis being resol, date, and time }


Chris

 

[Matt Allington]

I would be happy to take a look if you can provide a sample workbook. It is too hard to work out what is going on otherwise.

 

[cme263]

Thanks Matt,
I tried to keep he above generic and a bit simpler. Forgive my ignorance, but not able (or allowed) to attach a file. Do I just post my email address or insert in into the 'Trackback:'?
Chris

 

[Matt Allington]

Well I normally just load samples to dropbox and post a public link. But you can email me.

 

[Matt Allington]

Chris, you will have to help me understand this better.

I need to bin the data by selectable time resolutions (res_bin[resol]), and chart it against the clean equivalent resolution time elements.

I don't really know what you mean here. What do you mean "Selectable". Are you saying it needs to be "interactive" where they user can change the bin sizes? or something else?

What is a "clean equivalent resolution time element"? I don't know that that means.

What is in your data tables? You have a table of Queue length and another of people count. What is each row in these tables? Each row has a single timestamp, but how can you measure resolution time if there is only a single time stamp? Surely you need a start and end time stamp.<values([high]))),

</values([high]))),

 

[cme263]

Yes, interactive. In each of my data tables, there are attributes reported every 5 minutes. With a simple Sum:=sum([attribute]), a pivot table will nicely filter that Sum for the various legend fields (site, camera, etc) against a date and time in the series fields. This charts as expected, with the series plotted every 5 minutes. Plotting the sum versus the count for example. I have a need to calculate some averages and some maximum in ranges too.

I have added a reference table 'res_bin', where I have defined my bins, i.e. a (60, 30, 15...) minute 'resol' bins that that define that start and stop time stamp on the minutes of the hour. So a 30 minute 'resol' filter the 'Sum' and bin for the '0' min, all the filtered times >= 0 and less than 30, while the bin for '30' would sum for >= 30 and less than 60. When this plots, I want the time resolution of the series plot to just have 0 and 30 in the series as the minor level of granularity, not the 5 minute.

I keep getting an error what I try this with the examples I have found. I am clearly not understanding what values is doing, and thinking that sumx might be part of the answer. So yes, interactive binning... and that is where I say selectable from a field that would be selected. (I also found the use of the hasonevalue, so that this would only show data with one bin selected, because if 2 (or all) were selected.. I would expect an error). If you have an idea or example to provide and display data with interactive binning.. any help would be much appreciated.

 

[Matt Allington]

Ok. I assume when you say selectable, you mean the user can select from a predefined list of bin sizes that don't change, is that correct?

I'm not ready to even look at the dax yet until I understand the tables. Can you answer my other questions above too?

 

[cme263]

Yes, a predefined list of bin sizes (I have defined in the 3rd table titled 'res_bin')
So the first 3 columns identify the source of the reporting data - and they are to become the legend data for the pivot tables.
There are then different types of attribute data (assorts counts of analytic data) and the last 2 columns are of all things Epoch dates.
I then add some columns to convert to a real local time (time_stamp_in) followed by 4 derived columns just to recognize the date, time, hour and minute - as want to be able to use the date and times in the category axis in the pivot tables.
There will be many pivot tables produced for assorted totals, averages, and maximums for the predefined time resolution bins (binning attribute data per the predefined resolution time that sets the binning by the reported minute times).

When I said "clean equivalent resolution time element", what I mean is that I want to see just the series entry on the pivot table and chart for the resulting bin totals. For example, if a resolution time of 30 is selected, then bin sums the 6 rows of data (from 0, 5, 10, 15, 20, 25 minutes) would only be tabled (and charted) for 0, and 30 would be 30 to 60 minutes. Only the bins for 0 and 30 being tabled and not all the 5 minutes increments.

I think that answers all your questions.

 

[Matt Allington]

You say you are struggling to follow the pattern Dynamic Segmentation – DAX Patterns The problem I see is your data is not in the same shape as that used in the pattern. You need to prepare your data first otherwise the pattern is not usable. The pattern has a single table with 1 row per product plus qty, price (to calculate total sales) and then a measure for the binned range that works out the bins based on the total sales. You on the other hand have 2 tables that each contain a time stamp. There are a number of dwell time columns in 1 table, but it is not clear (to me anyway) what these are or how they relate to each other etc.

Can you get a table that looks like the one in the pattern? Also it is not clear to me what each row is in each table. If each row represented a customer, then I guess it is workable. But it doesn't look like that - is it a snapshot of how many people are in the queue? If so, I don't see how you can copy this pattern with the data you have got. If there a 6 snapshots in 30 mins (5 minutes each), and each time there are 5 people in the queue, how do you know if it is the same 5 people or different people each time.

 

[cme263]

I love the way you think... at this point I am not trying to connect the 2 data tables. They should be, be do not have to be as they are generated at separate time and do not have to be related yet. They can and will come from different sources. And regarding data content... lets not go thee for now. Many attributes are being .... challenged and explored .
I am confident that the simple binning can be done, but it is the displaying of just the binned times that is killing me. So just dealing with one data table as is.. what is the magic expression? We can add derived columns as needed.