locked
PowerPivot Filter Data RRS feed

  • Question

  • Hi, if I import data to a powerpivot table and filter it using the column filters in the powerpivot window, that filter should get applied in the pivot table in the workbook?

    For example, if I import all the internetsales data from adventureworks and filter in powerpivot to only show the sales from California, in the pivot table in the workbook if I put the Sum(SalesAmount) in the values it gives-me the sales amout of all the sales in all states and not only from California...

    Is this a bug or a limitation? Because if I want to filter all the sales from california i need to put the filter in the query and that for a common user might be complicated.

    Thanks
    Rui Romano
    Monday, December 7, 2009 2:32 PM

Answers

  • Hi Rui,

    I can see the confusion around two filtering.

    1) Filter during Import let's you get data for the subset you wish to work on. For example, if you say I want sales between Jan 09 to Jun 09 then we will import that data specific to that date.
    2) Filter on PowerPivot Window is just for your quick view. Let's say you have millions of rows imported to your system and you would like to see data pertain to some condition then the filter on PowerPivot let's you do that.

    PivotTable works on the data you have pulled into the model and if you wish to apply filter to your results set then you can use slicers or regular report filter values.
    Hope this provides some clarification.

    Thanks,
    Deva [MSFT]



    Deva
    Tuesday, December 8, 2009 4:17 PM

All replies

  • Hi, Rui,

    Pivot table will use the entire PowerPivot workbook tables as the data source. The filter on the workbook will not be applied to pivot table or chart. In order to see only the data for California, you can add the state column in the horizontal slicer or vertical slicer inside Gemini Task Pane and select only California. Or if your pivot table would not need data from any other state at all, you can set the filter through the Table Import Wizard GUI when import the tables from the data source. There will be no query needed in either of these two scenarios. Please let us know if you have any question.

    Thanks!
    Lisa
    Monday, December 7, 2009 8:32 PM
  • Thanks for the reply.

    Ok, but what's the reason for the filters inside the powerpivot window? I think it will be logical if I apply a filter in the powerpivot window that filter get applied in the workbook pivot table...

    Thanks
    Rui Romano
    Tuesday, December 8, 2009 2:42 PM
  • Hi, Rui

    Your question is good. Based on my testing, when I applied a filter on the data when importing to the powerpivot book, the filtered data persists if i continued to create new pivot tables. For example, if i only filtered France and Germany sales data when exporting to the powerpivot book, what i can use in the future is only the data from these 2 countries.

    Therefore, the powerpivot filter does make sense to filter the data beforehand.

    Thanks.
    Peng
    Tuesday, December 8, 2009 3:40 PM
  • Hi Rui,

    I can see the confusion around two filtering.

    1) Filter during Import let's you get data for the subset you wish to work on. For example, if you say I want sales between Jan 09 to Jun 09 then we will import that data specific to that date.
    2) Filter on PowerPivot Window is just for your quick view. Let's say you have millions of rows imported to your system and you would like to see data pertain to some condition then the filter on PowerPivot let's you do that.

    PivotTable works on the data you have pulled into the model and if you wish to apply filter to your results set then you can use slicers or regular report filter values.
    Hope this provides some clarification.

    Thanks,
    Deva [MSFT]



    Deva
    Tuesday, December 8, 2009 4:17 PM