locked
How to filter table data after its loaded to PowerPivot window? RRS feed

  • Question

  • Hi, 

    I want to load the table data first and then i want to filter those rows (where condition) depends on the reports. Here I can use same data source for various reports which can be build in different excel sheets. So my question is, can we apply where condition after the data is loaded to powerpivot window? Please advise me...  

     

     

    Instead of filtering the data before loading it from the database, 

    Tuesday, February 8, 2011 4:00 PM

Answers

  • Hi Naushad

    You can load one table into PowerPivot multiple times. You can do that by loading the table as normal, and then going again to the table import wizard and selecting the same table again.  Perhaps a good approach to your requirement is to load the table twice, each one with the necessary filter (that is, specifying TaskTypeId=1 in the SQL query, and then load the table again but with TaskTypeId=2 as a filter).  In that way, you will hold all the data in-memory yet will be able to build the reports without the user having the filter the data manually.




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    • Marked as answer by naush83 Thursday, February 10, 2011 9:12 AM
    Thursday, February 10, 2011 1:36 AM
    Answerer

All replies

  • You can filter the PowerPivot window tables after loading the data by clicking on the button next to each column name,  You will see the option to filter the data, either manually select the members you need to keep or apply a filter depending on the column data type. 




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, February 9, 2011 1:08 AM
    Answerer
  • Hi Javier,

              Thanks for the reply. But it seems this filtering option doesn't help me.  I think I haven't made it more clear to you. Let me explain the scenario with a an example.

    I have a Task table which basically contains two types of task data. The column TaskTypeID in Task table is used to distinguish between these data. Now I have to build two separate reports - one is based on Task data of type = 1 and  another is on Task data of type =2. I don't want user to select the Task Type through a column filter or slicer, since this type classification is just based on a program logic and the user is not concerned with it . So I loaded the whole data from Task  table (Select * from Task) and I wanted to use this same data source for both reports which I an build in different Excel sheets. So I have to filter the data source after its loaded depends on the reports like :-      For Report 1- Select * from Task where TaskTypeId = 1,   For Report 2 - Select * from Task where TaskTypeId = 2.

    My Question is how can apply this 'Where condition' once the data is loaded? Is there any filtering technique used by DAX functions? Please advise me. 

     

    Thanks,

    Naushad 

     

    Wednesday, February 9, 2011 7:52 AM
  • Hi Naushad

    You can load one table into PowerPivot multiple times. You can do that by loading the table as normal, and then going again to the table import wizard and selecting the same table again.  Perhaps a good approach to your requirement is to load the table twice, each one with the necessary filter (that is, specifying TaskTypeId=1 in the SQL query, and then load the table again but with TaskTypeId=2 as a filter).  In that way, you will hold all the data in-memory yet will be able to build the reports without the user having the filter the data manually.




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    • Marked as answer by naush83 Thursday, February 10, 2011 9:12 AM
    Thursday, February 10, 2011 1:36 AM
    Answerer
  • Hi Javier,

     

     Thanks a lot. This would work well for my requirement. But, is loading one table multiple times not an overhead? Anyway, thanks for the link to your Blog too.

    Thursday, February 10, 2011 9:11 AM
  • Naushad,

    I don't believe it would be an overhead, as you are loading half the table each time (so you would end up with two in-memory tables, but each table is half the amount of rows of the real physical table).   So at the end of the process, you would have the same amount of rows that if you would have loaded the entire table only once. 




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, February 10, 2011 10:53 AM
    Answerer