none
PowerPivot Filter by list of value

    General discussion

  • Hi There,

    Here is my scenario, we have daily history tranaction table with entries of all product sold, we have 30 day history in that table, user typically research by day and list of productID. 30 day history table right now is about 3 million records.

    I create view at database, then powerpivot 2010 is used to import that view. I can tell user to do filter by day during the import (say 06/18/2013), this will import records for that day.

    However users also want to search by list of product by product id, sometimes it is just 20 product list, sometimes it is 1,000 productID.

    Is there a way in PowerPivot that user can drop list of productID, so they can get the result back?

    Thanks


    Don

    Wednesday, June 19, 2013 1:42 PM

All replies

  • You can bring all records in Powerpivot for Month to current date or for a day.

    Create a dummy Search table in Powerpivot to hold ProductIds  joined to ProductId of Dimension/fact table you are bringing in Powerpivot model.

    Populate/refresh this table from Excel sheet or whichever way you want user maintain the list of product ids they want to see results for. 

    You can also maintain a similar search table in database and provide a simple frontend for users to update the same. Then you can bring both tables from database to powerpivot for reporting.

    Use Search table productids in filter of pivot table/power view report.

    Wednesday, June 19, 2013 11:55 PM
  • Hi MM-99,

    Thanks for quick reply. Yes, I import fact fact table into Excel by filtering only current day.

    I also create Excel worksheet with one column "ProductID", with all productID as you advice here. I import ProductID worksheet into Main Fact table. Then I join productID of my search table with productID of main fact table. Somehow it is not working when I drop ProductID talbe as filter. Nothing gets filter out.

    Where do I miss?

    Thanks again


    Don


    I think I find the issue, when link the search table in excel into powerpivot, then drop as filter in pivot table, it seems there is blank or null value at end of the list, so if I uncheck that null, then it works.

    Wondering how to get rid of that.

    Thanks

    Don

    • Edited by Dz0001 Thursday, June 20, 2013 4:18 PM find issue
    Thursday, June 20, 2013 1:25 PM
  • Does anybody have other suggestions for Don?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 22, 2013 6:43 PM
  • Don, is this still an issue?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, September 13, 2013 7:04 PM