How to return list of remaining (missing) values per month?

[SonicYouth]

My goal is to display a list of employees that do not have a corresponding record (i.e. have not completed monthly task).

In one table, I have the records with employee name, completed task, date completed, etc. as so

Employee	Task	Month
A	1100	January
B	1300	January
B	1200	February
C	1400	February
D	1500	February

The second table is a master list of all employees,

Employee

A

B

C

D

E

How can I return a list of employees that have not done the monthly task per month? For example, for January, employee C,D, and E have not completed a task; and for February, employee A and E.  I've tried the EXCEPT function but was not able to incorporate a month slicer to differentiate the months; it compares the entire list from the first table with the second table (leaving only employee E remaining).

Thank you for any help.

[v-yangliu-msft · Accepted solution]

Hi  @SonicYouth ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('Table1'[Month])
var _column=SELECTCOLUMNS(FILTER('Table1','Table1'[Month]=_select),"em",'Table1'[Employee])
return
CONCATENATEX(
    FILTER(ALL(Table2),
    NOT('Table2'[Employee]) in  _column),'Table2'[Employee],"-")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[sjoerdvn]

create a measure, something like this:
# task = COUNTROWS(your_task_table) + 0

Now create a visual (list or grid), drag in month and employee columns
add a filter on the visual with the measure,  [# task] is 0