locked
PowerPivot: Setting default values for filters RRS feed

  • Question

  • Hi everyone,

    I am looking for a way to set default values for columns in case they are used as filters.

    Assume I want to select a column as filter in the right pane of my pivot table. Then, by default there is no selection chosen, but all data is shown. I am looking for a way to set a default selection so that an other user directly sees my selection. Is there a way to achieve this?

    Thank you very much!

    Best regards,

    Clemens  

    Monday, October 6, 2014 10:12 AM

Answers

  • Unfortunately there is no concept of DefaultMembers in Power Pivot or SSAS Tabular. the best you can do is to create a calculation like this to e.g. set the default-year to 2007:
    DefaultYear:=IF(ISFILTERED('Date'[Calendar Year]),
    SUM('Internet Sales'[Sales Amount]),
    CALCULATE(SUM('Internet Sales'[Sales Amount]), 'Date'[Calendar Year] = 2007))

    this yields the problem that you will also see the value of 2007 as a grand-total if you have your yours on rows 
    another disadvantage is that this has to be done for each measure

    but i am afraid there is no better workaround, sorry

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, October 6, 2014 12:45 PM
    Answerer

All replies

  • Unfortunately there is no concept of DefaultMembers in Power Pivot or SSAS Tabular. the best you can do is to create a calculation like this to e.g. set the default-year to 2007:
    DefaultYear:=IF(ISFILTERED('Date'[Calendar Year]),
    SUM('Internet Sales'[Sales Amount]),
    CALCULATE(SUM('Internet Sales'[Sales Amount]), 'Date'[Calendar Year] = 2007))

    this yields the problem that you will also see the value of 2007 as a grand-total if you have your yours on rows 
    another disadvantage is that this has to be done for each measure

    but i am afraid there is no better workaround, sorry

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, October 6, 2014 12:45 PM
    Answerer
  • Thank you very much!
    Monday, October 6, 2014 12:50 PM
  • Alternatively, if you can create a pivot table and save it before sharing with your team, you can simply apply the filters you want to the pivot and then save the workbook. Whoever opens the workbook will see whatever filters you saved.

    If you want to send an empty pivot table, you can put slicers on the table with your default selections made and they can then drag any fields they want on.

    Monday, October 6, 2014 4:05 PM