Set Analysis - how to control multiple filters

[tjeneusa15]

Hi, 

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?

Thanks!

[dwforest]

I think p() should work... hard to guess as you have not supplied compete data set, counting ??
Count({<[Responsible Department]=p([Assigned Department])>} ID)

[tjeneusa15]

Hi Dwforest, thank you for replying.

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.  

[pradosh_thakur]

Can you try the below

Count(distinct {<[Responsible Department]=p([Assigned Department])>} ID)

Count(distinct {<[Responsible Department]=$::[Assigned Department]>} ID)

[Ravi_Nagmal]

Try this

Count(distinct {1<[Responsible Department]=p()>+<[Assigned Department]=p())>}, ID)