Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter with ALLEXCEPT

I am trying to do a very simple measure, which should count rows for all the 'Open' requests. In doing so, I want all the exiting filters to be removed except the one(s) I don't want to. My measure looks something like this:

 

Number of Open Requests = COUNTROWS( FILTER( ALLEXCEPT(Table1, Table1[Group]), Table1[Status] = "Open"))
 
Here group is to whom the request is assigned to. So, if let's say, I have slicer on Group and if a value is selected from that group slicer, Open request count should be the no. of open requets for that group only. The above measure always give the total no. of open requests irrespective of what has been selected on the group slicer. 
 
Can someone please help to explain what I am supposed to do to get this working ? I reas somewhere if you ALLEXCEPT as a table, it will ignore the ALLEXCEPT. Not sure how this will work.
 
 
My data looks something likes this:
 
Request No. Status     Group
123OpenBI
124ClosedCCB
125CanceledDBA
126OpenDBA
127CanceledCCB
128ClosedBI
129ClosedCCB
130ClosedCCB
131OpenBI
132OpenDBA
133ClosedCCB
134OpenDBA
135OpenNetwork
136ClosedCCB
137OpenBI
 
So, if "BI" is picked in the slicer, row count of Open requests for BI should be shown i.e. 3
 
Thanks in advance,
SN
 
 
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    7 years ago

    OK. Then how about this?

     

    Number of Open Requests =
VAR SelectedGroups = VALUES(Table1[Group])
RETURN
COUNTROWS(
    FILTER(
        ALL(Table1),
        Table1[Group] IN SelectedGroups
        && Table1[Status] = "Open" 
    )
)

    This takes off all filters and then adds back the group selection and the status specification.

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    7 years ago

    Interesting question!

     

    The key here is that ALLEXCEPT removes all filters except for context filtering on the specified column. I'm guessing wherever you are evaluating your measure, there is no context filtering to preserve, so telling it to keep the context filtering on the Group column doesn't do anything useful.

     

    Try this instead:

     

    Number of Open Requests = COUNTROWS( FILTER( Table1, Table1[Status] = "Open" ) )

    In this, the Table1 argument is automatically filtered by your slicers unless otherwise specified.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks AlexisOlson for your help here. This may not work for my requriement as the measure should only consider the "Group" slicer and ignore the other slicers ie. the user can pick another status or a year from Year slicer etc. My need is irrespective what slicers are picked, "Currently Open Requests" should always show the open requests for the selected group. Hope I am clear with my requirement.

       

      Please let me know if you need any thing else.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        7 years ago

        OK. Then how about this?

         

        Number of Open Requests =
VAR SelectedGroups = VALUES(Table1[Group])
RETURN
COUNTROWS(
    FILTER(
        ALL(Table1),
        Table1[Group] IN SelectedGroups
        && Table1[Status] = "Open" 
    )
)

        This takes off all filters and then adds back the group selection and the status specification.