Forum Discussion
agur
Advocate I
8 years agoDAX ALL respect slicer
I have a report with page level filter "Last 12 calendar months"
I have a slicer which filters on a field called Department
I have a calculated column which looks like this:
ActualCloseMonth12MonthsAgo = DATE(
YEAR(EDATE(Opportunities[Actual Close Date], -12)),
MONTH(EDATE(Opportunities[Actual Close Date], -12)),
1
)
I have a line with "12 months rolling value" measure which looks like this:
Rolling 12 months total = CALCULATE(
SUM(Opportunities[Actual Value]),
FILTER(
ALL(Opportunities),
AND(
Opportunities[Actual Close Date] >= MAX(Opportunities[ActualCloseMonth12MonthsAgo]) &&
Opportunities[Actual Close Date] <= MAX(Opportunities[Actual Close Date]),
Opportunities[Status] = "Won"
)
)
)Everything works and gives the right information, but I can't use the slicer (obviously) because I use the ALL() to get the data outside of the page level filter.
Any tips on how to change the query to make the slicer work as well?
You can try and replace ALL(Oprtunities) with ALLEXCEPT(Opprtunities[Department])