Forum Discussion

agur's avatar
agur
Icon for Advocate I rankAdvocate I
8 years ago
Solved

DAX 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])

2 Replies

  • You can try and replace ALL(Oprtunities) with ALLEXCEPT(Opprtunities[Department])

    • agur's avatar
      agur
      Icon for Advocate I rankAdvocate I

      Thank you!

      You managed to help and make me feel stupid with one sentence :smileyhappy: