I am trying to build a department count KPI where the count is the same when a department is selected from 2 different filters.
Here is a sample data set: it's a many to many relationship
Assigned Department
Responsible Department
Dept 1
Dept 1
Dept 2
Dept 2
Dept 3
Dept 3
When a department is selected from Assigned Department, the count derived (i.e. 5) is the correct count. In this example, I'm selecting Dept 1 from Assigned and the associated records in Responsible are Dept 1, Dept 2, and Dept 3. However, the count is 5 because the set analysis is only taking into account Assigned Department.
Assigned Department
Responsible Department
Dept 1
Dept 1
Dept 2
Dept 2
Dept 3
Dept 3
I need help with the expression so that when I also select the same department from the Responsible column, that the count will behave the same way, which is to take only Dept 1 into consideration. Right now the count becomes 10 because it includes Dept 2.
Assigned Department
Responsible Department
Dept 1
Dept 1
Dept 2
Dept 2
Dept 3
Dept 3
It is correct to assume that both columns contain the same departments. I tried using E() and P() but that doesn't work for Responsible Department. I still get 10 instead of 5.
Could anyone provide guidance on how I can force this count to work?
I think p() should work... hard to guess as you have not supplied compete data set, counting ?? Count({<[Responsible Department]=p([Assigned Department])>} ID)
The expression is was trying to use was Count({<[Assigned Department]=p([Responsible Department])>} DepartmentEmployees).
However, as I explained before, selecting a Department from Responsible Department would cause the count to include the employees in the departments that showed up in the Assigned Department.
Oddly, this expressions seems to be working: COUNT( DISTINCT IF([Responsible Department] = [Audited Department] ,DepartmentEmployees))
I don't understand how the IF statement is creating the situation that I'm looking for. I need to clean up the data set and will post as soon as I can. If anyone could provide insight on what the IF statement is doing, I'd appreciate it.
