Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cannot slice because of function ALL

I am displaying a table below on my report:

 

 

The rows are measures calculated from Table T1. The measure used to calculate the bottom row is:

 

CALCULATE(sum(T1[Count]), FILTER(
                    ALL(T1),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6
                                 && T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))
                )
 
The purpose of this measure is to only count when dateend from T1 falls within the selected week in the table above.
 
The problem I am having is that I want to filter by userid in T1. When I selected a value from a slicer which the field is userid, the table above does not change. 
 
What I can do so that my slicer with userid will work with the table above?
Need Help
  • sebastiandyrby's avatar
    sebastiandyrby
    3 years ago

    Hi

    I'm guessing you could replace the 'ALL(T1)' statement in your measure calculation with the ALLEXCEPT function. As stated in the documentiation (ALLEXCEPT function (DAX) - DAX | Microsoft Learn), this "Removes all context filters in the table except filters that have been applied to the specified columns".

     

    So if you replace ALL(T1) with something like ALLEXCEPT(T1, T1[userid]), that would probably do the trick.

     

    Best regards,

    Sebastian

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • sebastiandyrby's avatar
    sebastiandyrby
    Resolver I
    3 years ago

    Hi

    I'm guessing you could replace the 'ALL(T1)' statement in your measure calculation with the ALLEXCEPT function. As stated in the documentiation (ALLEXCEPT function (DAX) - DAX | Microsoft Learn), this "Removes all context filters in the table except filters that have been applied to the specified columns".

     

    So if you replace ALL(T1) with something like ALLEXCEPT(T1, T1[userid]), that would probably do the trick.

     

    Best regards,

    Sebastian

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      After some further digging, I discovered that my slicer was user T1[username], not T1[userid]. I added T1[username] in the ALLEXCEPT columns and it worked!

       

      My final query was:

      CALCULATE(sum(T1[Count]), FILTER(
                          ALLEXCEPT(T1,T1[userid],T1[username]),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6
                                       && T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))
                      )
       
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Sebastian,

       

      Thanks for your response. I modified the query to:

       

      CALCULATE(sum(T1[Count]), FILTER(
                          ALLEXCEPT(T1,T1[userid]),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6
                                       && T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))
                      )
       
      The slicer on userid does not change the numbers in the table.