Thought I'd give a quick bump to this one in case it had fallen off the ever so quickly growing list of posts. I'm still struggling with this particular problem. Thanks for taking a look.
This is a tricky one I've struggled with myself. There might be a better way to approach this situation than what I've found, but I have started creating Sets for those projects (or in my case users) that fit a certain criteria. Then I use the Set to filter data. I've also used table calculations before, but those can get cumbersome if you have a lot of rows.
If you aren't importing the data directly into Tableau from something like an ODBC, another way to do it is to attach a new variable signifying whether it has a Hold/Cancel event or not to the data before bringing it into Tableau. Basically, create the list of projects that have a Hold or Cancel event (just the name or identifier of the project, nothing else), then create a new variable to add to your original set of data. In other words, you would create the list, create another column that says something like "Yes" or "True", and then use Access or something similar to join your original data set with the list of projects with the given criteria. That way you'll have a column with a null if there is no Hold or Cancel event and a "Yes" or "True" if there is. In this case, make sure to setup the join in a way where all of the original data is kept and Holds/Cancels only join where the project identifier is equal in both tables (so a left join). Otherwise, you could lose any rows where a project does not have a Hold/Cancel.
I hope this helps! Let me know if any of this is unclear. I'll make sure to follow this thread in case anyone has a more comprehensive way of dealing with this situation since I run up against this problem fairly regularly.
Thanks so much for the help. I, too, had considered going outside of Tableau to do this in the SQL datasource that I'm using, but was trying to figure out a way to do this inside of Tableau instead of outside.
I had not considered Sets, as they aren't my typical go to piece of functionality. I was able to get this to work using that method, so thank you very much. It looks like that will quickly become a more used tool in my toolbox.
Happy to help! Glad to hear that method worked for you. I'm hoping a future release at some time will make data aggregation situations like this more straightforward and easier, but for now at least there are a few ways to get around it.