locked
PowerPivot fiter to slice data per hour, day or month RRS feed

  • Question

  • 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?

    Tuesday, March 31, 2015 7:19 AM

Answers

  • 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)


    Imke

    Tuesday, March 31, 2015 6:11 PM
    Answerer
  • 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.


    Imke

    • Marked as answer by Charlie Liao Wednesday, April 8, 2015 2:23 PM
    Sunday, April 5, 2015 4:37 PM
    Answerer

All replies

  • 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)


    Imke

    Tuesday, March 31, 2015 6:11 PM
    Answerer
  • 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.


    Imke

    • Marked as answer by Charlie Liao Wednesday, April 8, 2015 2:23 PM
    Sunday, April 5, 2015 4:37 PM
    Answerer
  • 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.

    Wednesday, April 22, 2015 7:01 AM