none
DAX CALCULATE and ignore slicer selections?

    Question

  • I'm working in SSAS Tabular, not PowerPivot.  Hopefully this is the right place to post DAX questions.  Using the sample "AdventureWorks Tabular Model SQL 2012":

    I'd like to create a measure that always shows the Reseller Sales Amount for FY 2008, regardless of any slicer selections.  I've created a slicer connected to my pivot table for "Fiscal Year".

    With this formula, I see the 2008 reseller sales amount as long as the "Fiscal Year" slicer is not set, or if 2008 is selected in the slicer.  But if any other year is selected in the slicer, I see blank:

    Reseller Sales Amount 2008:=CALCULATE(SUM('Reseller Sales'[Sales Amount]), FILTER('Date', 'Date'[Fiscal Year] = 2008))

    My understanding is that the "ALL" function can be used to disregard slicer selections.  I can see that with this formula, the total reseller sales for all years is displayed, regardless of any selection on a "Fiscal Year" slicer:

    Reseller Sales Amount 2008:=CALCULATE(SUM('Reseller Sales'[Sales Amount]), ALL('Date'))

    So, how can I achieve the same indifference to slicer selections, but for the FY 2008 total rather than all years?  I tried this, but it didn't work:

    Reseller Sales Amount 2008:=CALCULATE(SUM('Reseller Sales'[Sales Amount]), ALL('Date'), FILTER('Date', 'Date'[Fiscal Year] = 2008))


    • Edited by almaplayera Thursday, August 29, 2013 8:42 PM
    Thursday, August 29, 2013 8:41 PM

Answers

  • this one should work

    Reseller Sales Amount 2008:=CALCULATE(SUM('Reseller Sales'[Sales Amount]), 
    FILTER(ALL('Date'), 'Date'[Fiscal Year] = 2008))
    
    

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by almaplayera Friday, August 30, 2013 2:57 PM
    Friday, August 30, 2013 10:37 AM
    Answerer

All replies

  • this one should work

    Reseller Sales Amount 2008:=CALCULATE(SUM('Reseller Sales'[Sales Amount]), 
    FILTER(ALL('Date'), 'Date'[Fiscal Year] = 2008))
    
    

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by almaplayera Friday, August 30, 2013 2:57 PM
    Friday, August 30, 2013 10:37 AM
    Answerer
  • You sir are a gentleman and a scholar.  Thanks!
    Friday, August 30, 2013 2:57 PM