Forum Discussion
Not in List Daily
Hi,
I have two tables, one static table with a list of names and IDs in departments, and another table that is an export of employees filling out a questionaire daily.
I was wondering if there was a way to create another table from the above table where it lists out who did not fill out the questionaire each day?
I am having trouble because I am not sure how to compare lists daily, instead of just comparing the full tables against each other.
Please let me know if this question does not make sense
Thank you!
Sarah
Anonymous Al is also in the ch station right so should display too?
Use ALLSELECTED() instead of ALL():
MissingForm =VAR formcomplete = VALUES(FormSubmissions[Employee ID])RETURNCONCATENATEX(FILTER(ALLSELECTED(Employees), NOT(Employees[ID] IN formcomplete)), Employees[Name], ", ")
13 Replies
- AllisonKennedy
Community Champion
Anonymous Al is also in the ch station right so should display too?
Use ALLSELECTED() instead of ALL():
MissingForm =VAR formcomplete = VALUES(FormSubmissions[Employee ID])RETURNCONCATENATEX(FILTER(ALLSELECTED(Employees), NOT(Employees[ID] IN formcomplete)), Employees[Name], ", ")AllisonKennedy Thank you so much for all of your help! I believe this is the solution. I will need to double check hundreds of names so that should take a while. But so far it looks like your formula worked. Thank you so much again!!
Sarah
- AllisonKennedy
Anonymous Sarah~
Yay! Glad it's working. This is a fun little example, so thanks for the challenge. 🙂
- PhilipTreacy
Super User
Hi Anonymous
the short answer is yes but you need an identifier to link both tables, like an employee ID. Can you share what your data looks like, change the names to protect the innocent.
Regards
Phil
- Pragati11
Hi Anonymous ,
You can do an ANTI JOIN in Power Query in Power BI on these two tables and find out the one that have not filled out the questionaires.
Check out the details here:
https://www.powerbi-pro.com/en/power-bi-seven-types-of-table-joins/
https://radacad.com/find-mismatch-rows-with-power-query-in-power-bi
It will nice to have some sample data to have more clear picture on your issue with some screenshots as well.
Thanks,
Pragati
Hi PhilipTreacy and Pragati11
are you able to see the sample data if i share this google sheets link?
https://docs.google.com/spreadsheets/d/1WIlPdZf-BHOdU1SUmOyAWRpjJkjJHkL21sprF_cARbo/edit?usp=sharing
Thank you!
- Pragati11
Hi Anonymous ,
Just checked the sample data you shared.
Try joining these 2 tables in Power Query in Power BI using the "static[ID]" and "Questionaire[EmployeeID]" columns from your tables.
As you want the ones who haven't filled out a questionaire, create a left anti join from "Static" table to "Questionaire" table.
Refer the following link on how you can do this in Power BI:
https://radacad.com/find-mismatch-rows-with-power-query-in-power-bi
Thanks,
Pragati
