cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthnama Regular Visitor
Regular Visitor

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
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
AlexisOlson Member
Member

Re: Filter with ALLEXCEPT

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 5
AlexisOlson Member
Member

Re: Filter with ALLEXCEPT

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.

srikanthnama Regular Visitor
Regular Visitor

Re: Filter with ALLEXCEPT

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.

Highlighted
AlexisOlson Member
Member

Re: Filter with ALLEXCEPT

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.

srikanthnama Regular Visitor
Regular Visitor

Re: Filter with ALLEXCEPT

Thanks a bunch @AlexisOlson for your quick responses and help. This is what I was looking for.

 

PS: I seem to be struggling with how all the contexts work. Can you give some insights on how to get better at this ? Like a course, books, blogs etc.,, May be sharing your own experience in picking and mastering DAX will help.  I defiently feel practicing and implmenting the concepts play a big role but knowing the fundamentals is the key stepping stone.

 

Thanks again and happy holidays!!!

AlexisOlson Member
Member

Re: Filter with ALLEXCEPT

The Definitive Guide to DAX would be my book recommendation. Chapters 4 & 5 are key to understanding filter context.

 

In addition to that book and building reports for work, I've picked up DAX mostly by helping others with interesting real-world problems (mostly on Stack Overflow but here too recently) that I don't necessarily know how to solve beforehand. 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 302 members 3,032 guests
Please welcome our newest community members: