I have an Excel database file that contains the total passenger passes from a specific location. The total number of passenger passes is counted in a period of 2 minutes(e.g. 14:45:00 to 14:46:59). I have imported my database into PowerPivot and have also created relevant PivotTables and PivotCharts with some slicers to analyze them. How can I create a slicer which filters data in greater periods of time like hour, day or month?

Hi Reza,

there are some possibilities:

1) Create a date-table (DimDate) with one line per 2-minute-intervall, adding hours, day, month in columns and connect your FactTable

2) Use bucket approach to create calculated columns in your FactTable (hour, day, month..): http://blogs.msdn.com/b/analysisservices/archive/2014/06/06/bucketing-values-in-dax.aspx

(you could create separate bucket tables for hours, days, montht, year - that way you don't create so many lines - but don't know it this really makes sense, as PP can handle long tables easily)

Thanks,

• Edited by Reza Hasanzadeh 20 hours 15 minutes ago

Thanks,

• Edited by Reza Hasanzadeh Saturday, April 04, 2015 10:49 AM

Hi Reza,

just in case you need a way to dynamically create that DimDateTime, have a look at my latest blogpost: http://www.sqlxpert.de/dimdate-mit-power-query-erzeugen/

Just check the dates selected and refresh the query (in any case). You could also set the EndDate in cell E10 to: TODAY(), that way the table would always grow to the current day.

Thanks everybody,

I managed to get larger times by adding columns like hour, day, ... to my table and having these columns extract the specific portion of the time range.