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”
<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
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>
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