Filtering a Calculation

[Anonymous]

I would like to calculate a count for a data set removing portions of a cross filter.  Below is a example of the mapping relationship.

Table1 

Department Code     Group Code

AAA                          111

BBB                          222

BBB                          333

CCC                         444

Table2

Department  Department Name

AAA               Administration

BBB               Finance

CCC               Operations

Cardinality = Many to 1 & Cross Filter Direction = Both

When the report is filtered at the page level by Group Code, I would like to show a count of all records for the corresponding Department regardless of the Group Code. 

EX.  Page Filter is set for Group code 333.  Trying to return a distinct count for all [No_ ] where Department is BBB (333's default department).  Assume 45 people assigned as BBB, 333 and 10 assigned as BBB, 222.  I am trying to return 55.

 I tried the formula below but it shows the count of all [No_] for every department unless Department is added to the visual.  

Count = CALCULATE(DISTINCTCOUNT('Employee'[No_]),ALL('Table1'[Group Code]))

[v-danhe-msft]

Hi @Anonymous,

Could you please post your 'Employee' table and the relationship between these tables? Due to I could not understand what your desired result, based on my test, it should show the count of the department 'BBB', you could also download my pbix file to have a view.

Regards,

Daniel He