Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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
Not applicable

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.

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.

Anonymous
Not applicable

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!!!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.