How do I count matches in a related table. I am feeding DAX the wrong row context. Help please

[I_Like_Pi]

I have an employee table that links to 3 other tables on EMP#. The employee table has all employees where as the other tables only have a smalll portion. I want to add an employee filter to a report page, however if I straight up use the employee table I will end up with a lot of extra values in the filter that lead to no data. I cant use the emp# from any of the linked tables because there is no gaurentee they share the same employee.

All of my relationships are linked 1way from the employee table.

I was trying to build a countrows filter in the employee table to the other tables but I either get a number that is the total pop or 1 no matter what. I tried defining the column below in the EmpTbl. It returns the total pop of Tbl1 in every cell.

InTables = COUNTROWS(
                   FILTER(
                      'Tbl1','Tbl1'[EMP#]=RELATED(
                         'EmpTbl'[EMP#])
                      )
                   )

As an aside I have a business unit table that bridges those same table, this originally create amiguity issues until I made all relationships 1way.

[I_Like_Pi · Accepted solution]

So I created the following column in the EMPTbl and then added it (InTables = Y) as a filter of the filter on my report page.

I am still running into the issue of BlankRow not returning N, but I dont really care about the no's. I could probably nest it in somthing.

InTables = 
              IF(
              COUNTROWS(FILTER('Tbl1','Tbl1'[EMP#]='EmpTbl'[EMP#])+
              COUNTROWS(FILTER('Tbl2','Tbl2'[EMP#]='EmpTbl'[EMP#])+
              COUNTROWS(FILTER('Tbl3','Tbl3'[EMP#]='EmpTbl'[EMP#])>0
              ,"Y","N")

[I_Like_Pi]

uhg I think I found it, not that I totally understand. I dropped the related function. I think that was pushing the perspective out to the other table and then summing the number of rows that found a match back ie the total pop because indeed there is a match.

I'm not going to resolve this until I confirm I didn't create some other mess. I also need to deal with the fact that when the filter returns no rows I am getting a blank. I would prefer a 0.

InTables = COUNTROWS(
                   FILTER(
                      'Tbl1','Tbl1'[EMP#]='EmpTbl'[EMP#]
                      )
                   )