Maximizing PowerPivot Performance and Filtering question RRS feed

  • Question

  • 1. Is there a way to assign local computing resources(threads, priority, RAM) to speed up the performance of PowerPivot? This tool is locks up my computer when I'm using it. Will the performance be greater or worse if I use 64 bit instead of 32 bit?

    2. I pulled some data but I need to filter items in this picture below. Can I get some suggestions on how to remove the highlighted areas?

    What I am struggling to do is when I expand down the levels: OperatorName->InvoiceYear->MonthName, the products purchased in that month are listed. However, because of the large dataset, I would like to hide rows where the [Total Cases] = 0. I have not been able to do this successfully. I have a functional solution in [Measure 4] where I use FILTER(). However, this seems to calculate on the row level and the speed is unbearably slow and therefore not an acceptable solution.

    • Edited by ResidentX10 Thursday, October 11, 2012 8:50 PM
    Thursday, October 11, 2012 8:29 PM


  • 1. Moving to a 64 bit is definitely going to help you, especially if you have more than 3 GBs of RAM.

    2. Can you make a calculated column in the model, something like

    if(isblank([OpenCases])=TRUE, BLANK(), [OpenCases])

    and then use this calculated column on your columns? This way, all zero rows would automatically be filtered and also, since this is already calculated and stored in the model, you shouldnt be facing a performance issue when you are querying. 

    You might have to do it for all the measures you want to use in your columns (could hide the original measures and just use these calculated columns instead of the originals.)

    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by ResidentX10 Friday, October 12, 2012 4:49 PM
    Friday, October 12, 2012 2:22 PM