'Dax filter for specific scenario

To help prioritize permit application review, I want to know which department is the last to review an application when there is more than one department assigned to review it. I have a list of the application numbers, departments and the corresponding status of the department.

AppNum Dept DeptStatus
985211 BIOL Request
985211 DENG Pending Review
985211 FIRE Request
985211 FPSV Request
985211 PLAN Received Back
985217 BIOL Request
985217 DENG Pending Review
985217 PLAN Pending Review

The logic in my head is to first find when the count of AppNum is greater than one. Then look at the DeptStatus and for when "Pending Status" is only one of those rows.

So in my example table, AppNum 985211 DENG would be included, but AppNum 985217 would not because more than one Dept has a pending status.

I have used a variety of COUNT and IF and SWITCH and I can get the pieces but then I lose the connection to the Dept information. I think I need a VAR and a FILTER but I can't figure it out.

Any help is appreciated!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source