Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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?
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
@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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |