Compare two lists (employee roster & audited list), filter with slicer

[bigchippah]

Hello,

I have two tables: an employee roster and a list of employees who have been audited.  I am currently comparing the lists with a Measure that returns a True/False if an employee's name is on both lists.  However, I would like to be able to sort my results in two ways: 1. by work location.  2. by date of audit.

Table 1: Employee Roster (all fake employee data)

Roster Date	Employee	Location
3/1/2021	Jones,One	Music Store
	Smith,Two	Music Store
	Johnson,Three	Music Store
	McDonald,Eight	Music Store
	Williams,Four	Toy Store
	Chen,Six	Toy Store
	Garcia,Seven	Toy Store
2/20/2021	Jones,One	Music Store
	Smith,Two	Music Store
	McDonald,Eight	Music Store
	Williams,Four	Toy Store
	Martinez,Five	Toy Store
	Chen,Six	Toy Store
	Garcia,Seven	Toy Store

Table 2: Audited List (again, all fake data)

Audit Date	Employee	Location	Result
3/1/2021	Jones,One	Music Store	Pass
	Johnson,Three	Music Store	Fail
	Williams,Four	Toy Store	Pass
2/24/2021	Jones,One	Music Store	Pass
	Smith,Two	Music Store	Fail
	Williams,Four	Toy Store	Pass

My desired result:

I would like to remove the Audited employees from the Roster, thus leaving me with a list of employees who HAVE NOT been audited.  However, this list would need to adjust according to the date (because the employee roster changes over time).  I would like to see something like this:

Table 3: Desired Result (Remaining Employees)

[Date Slicer:  March 2021 / Location Slicer: Music Store]

Name	Location
Smith,Two	Music Store
McDonald,Eight	Music Store

I have used a Measure to compare the lists and return a True/False value if an employee from the Audited list appears on the Roster.   However, this measure does not respond when I apply a slicer to choose between Locations:

Name is on Both Lists = 
var values2 = VALUES('Audited List'[Employee Name])
var values1 = SELECTEDVALUE(Roster[Employee Name])in values2
return FORMAT(values1, "true/false")

This measure shows the following:

Table 4: Result of my Measure

[Date Slicer:  March 2021 / Location Slicer: Music Store]

Roster Date	Employee	Location	On Both Lists
3/1/2021	Jones,One	Music Store	True
	Smith,Two	Music Store	False
	Johnson,Three	Music Store	True
	McDonald,Eight	Music Store	False
	Williams,Four	Toy Store	True
	Chen,Six	Toy Store	False
	Garcia,Seven	Toy Store	False

Can anyone help?

Thank you for reading!

[v-yangliu-msft]

Hi  @bigchippah ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _loc=SELECTEDVALUE('Table2'[Location])
var _date=SELECTEDVALUE('Table2'[Audit Date])
var _number=CALCULATE(MAX('Location Key'[Location2]),FILTER(ALL('Location Key'),'Location Key'[Location1]=_loc))
var _column=SELECTCOLUMNS(FILTER(ALL(Table2),'Table2'[Audit Date]=_date),"1",[Employee])
return
IF(
    MAX('Table'[Roster Date])= _date&&MAX('Table'[Location])=_number&&NOT(MAX('Table'[Employee]) in _column),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

If you need pbix, please click here.

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

[bigchippah]

Hello @v-yangliu-msft 

My apologies for not responding sooner, but I was out for a week with Covid.

Thank you for the solution.  When I try to apply it to my file, there are no employees flagged as 1, and so my field is always empty.

Can you explain the steps in the solution so that I can better understand how to apply it?

[johnt75]

Create a dimension table for store location like

Store Location = DISTINCT( UNION(
ALLNOBLANKROW( 'Audited List'[Location]),
ALLNOBLANKROW( 'Roster'[Location])
))

then create one-to-many relationships between the new table and both existing tables. Use the column from the new table on your visuals and slicers and it will filter both tables at the same time.

You may also want to consider creating an employee dimension table in the same way

[bigchippah]

@johnt75Hello, and thank you very much for your reply!

Can you help with this process if the naming conventions are different between the tables?  One table uses a string for the name ("Toy Store") and one uses an ID number (12345).  I have a "Location Key" table that I'm using to relate these two tables (and others). 

Table 1: Audited List:

Audit Date	Employee	Location	Result
3/1/2021	Jones,One	Music Store	Pass
	Johnson,Three	Music Store	Fail
	Williams,Four	Toy Store	Pass

But on my roster, the locations use an ID number:

Table 2: Employee Roster

Roster Date	Employee	Location
3/1/2021	Jones,One	12345
	Smith,Two	12345
	Johnson,Three	12345
	McDonald,Eight	12345
	Williams,Four	67890
	Chen,Six	67890
	Garcia,Seven	67890

If my naming convention isn't identical (Toy Store:12345 instead of Toy Store: Toy Store), how can I build one of these new dimension tables?

[johnt75]

It sounds like you already have a dimension table which includes the relevant info. If that table links to both the other tables, then using any column from that table should filter the others, so use columns from there on your visuals and filters