Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter All Loses Slicer Capability

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?
  • Anonymous's avatar
    Anonymous
    3 years ago

    The answer is to use ALLEXCEPT in place of ALL. Here was the final query that I used.

     

    CALCULATE(sum(T1[Count]), FILTER(
                        ALLEXCEPT(T1,T1[userid],T1[username]),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6
                                     && T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))
                    )
     

5 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Try Allselected instead of All

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This produced blanks in the table for the bottom row.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    try like:

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

      This produced blanks in the table for the bottom row.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    The answer is to use ALLEXCEPT in place of ALL. Here was the final query that I used.

     

    CALCULATE(sum(T1[Count]), FILTER(
                        ALLEXCEPT(T1,T1[userid],T1[username]),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6
                                     && T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))
                    )