Can someone help me figure out why this isn't working? I want to show only DEFECT_ID that have hit both 'Regional Clinical Informatics' and 'ART - AMB Refinement Team'.
=if(match(COM_CAT,'Regional Clinical Informatics') and match(COM_CAT,'ART - AMB Refinement Team'),DEFECT_ID)
If you used a table-chart and displays there COM_CAT and the DEFECT_ID you could see them in their context and you could try to apply a logic like I mentioned before (whereby you will probably need some TOTAL statement within the expression to get it to work by this granularity).
If I interpret your data right then they are chains of events - one ID changed their state several times on multiple dates. This is in general not easy to handle and I suggest to transfer as much as possible of the logic into the script (categorizing and flagging the multiple states and using multiple and/or canoncal calendars and/or as-of-tables) instead of trying to solve such tasks within the UI.
It's not clear for me where do you want to apply this expression and it's further quite hard to comprehend your datamodel - at least in a quick glance.
I think like above mentioned that you need to apply the logic of categorizing and/or at least flagging them and the chain of events within the datamodel and not trying to create this within the UI. Not everything possible in the script could be done in the UI and if it's often much harder to get and by larger datasets it might be not performant enough. Therefore, I suggest that you rethink your datamodel.
