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
I realize this is an old post but it's similar to an issue I'm trying to resolve, so maybe you can help.
I have a measure (sum_adj_sentiment_es) that looks at a Boolean attribute (is_entity_set) for each record in a fact table to determine if it should be included in the aggregation. This works perfectly if I select 'Y' in the is_entity_set slicer. However, it's blank when I select 'N' but the desired result is the same value so that it will be static for comparison, etc. I tried using the approach you recommended for the date example above with the _test measure - to no avail.
CALCULATE( Max( FactMoment[sentiment] ),FactMoment[is_entity_set] = "Y")
CALCULATE( Max( FactMoment[sentiment] ),Filter(ALL(FactMoment),FactMoment[is_entity_set] = "Y"))
Any other suggestions?
Thank you in advance!