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 l...
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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
AlexisOlson
Super User
7 years ago

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.