Before diving into the DAX here, it seems that there are some data modeling issues to address first. The way you’ve got the tables and the filtering set up doesn’t make a lot of sense to me. The first question I would ask is whether the Employee table is a fact table or dimension table? I’m inferring from your post that it’s the latter, which would be appropriate if the values for department assignment, building and/or full-time/part-time change frequently. If that’s the case I would set up a Department dimension table with the unique values for Department ID and Department Description that filters the Employee table. If those values do not change frequently, that I would consider merging all the information together into one Employee dimension table.
Once we get the data modeling issues resolved, I think the DAX will be much easier to address.
- Brian