Filter reports by reference table

[tomperro]

Hello,  I have two tables of data, employee and test info. The employee table is a list of employees with the tests they have taken. Test info table is a list of tests that I want to filter the employee table with. Employee table has employee Id and a test Id. Test table had test Id and test name.

 The employee table may have more test id's that I want to show in all my reports so I want to only show the test ids in employee that exist in my test info table.

is this done on the join or is there a way I can use the test info table as a filter on my pages?

example

test table 

Id.   Test name

1    Test 1

2    Test 2

7    Test 7

employee

employee id.   Test id    Expected result

9999                1            Show

9999                2            Show

9999                4            Do not show

9999                5            Do not show

9999                6            Do not show

9999                7            Show

[amitchandak]

@tomperro , if they are joined (1-M)

if(isblank(Countx(employee, related(Test[Test name]) )  ), "Do Not Show", "Show")

if they are not joined

if(isblank(countx(filter(employee, employee[employee id] in values(Test[id]) ) , employee[employee id])), "Do Not Show", "Show") 

[amitchandak]

@tomperro , if they are joined (1-M)

if(isblank(Countx(employee, related(Test[Test name]) )  ), "Do Not Show", "Show")

if they are not joined

if(isblank(countx(filter(employee, employee[employee id] in values(Test[id]) ) , employee[employee id])), "Do Not Show", "Show")