Forum Discussion
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.
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.
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.
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.