Not in List Daily

[Anonymous]

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

[AllisonKennedy]

@Anonymous  Sarah~

Yay! Glad it's working. This is a fun little example, so thanks for the challenge. 🙂 

[AllisonKennedy · Accepted solution]

@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])

RETURN

CONCATENATEX(FILTER(ALLSELECTED(Employees), NOT(Employees[ID] IN formcomplete)), Employees[Name], ", ")

[Anonymous]

@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

[Anonymous]

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

[Anonymous]

Hi! thank you but I do not think this works because I want to compare the list to the static list daily.. so I want an output of those who have not answered the questionaire each day in a new table

[Pragati11]

Hi @Anonymous ,

I am not sure why you are saying this will not work without trying it.

This is your STATIC data table:

This is your QUESTIONAIRE table which has just got Employee ID 1 to 7 who have answered the questionaire:

Now I created an ANTI JOIN in Power BI as I suggested above. I end with all the employees who have not answered any questionaires:

Let me know if this is not what you are expecting. If not, then share the expected output based on the sample data that you have shared.

Thanks,

Pragati

[Anonymous]

Sorry, but I DID try this before I posted this question on this site.

This does not work because like you said, it outputs all of the employees that have not answered the questionaire at all.. I would like to see employees that did not fill it out each day. 

The above screenshot is what I want my output to be, but in table form.

[AllisonKennedy]

@Anonymous  I'm not sure what you mean by 'but in table form'. I have created a measure as they are more dynamic and efficient, but please let me know if that doesn't work: 

MissingForm =

VAR formcomplete = VALUES(FormSubmissions[Employee ID])

RETURN

CONCATENATEX(FILTER(ALL(Employees), NOT(Employees[ID] IN formcomplete)), Employees[Name], ", ")

You can see the result in the attached file below signature.

[Anonymous]

Hi  thank you!

I have another question.. Below is an additional name and column in your attached file for the "Employees" table. I added "Sarah" and a "Station" column... 

ID Name Station
1 Al ch
2 Bob nw
3 Charlie se
4 Dave sc
5 Sarah ch

In your FormSubmission table, Al who is from CH station, filled out the form on Dec 1 and Dec 2. Sarah is also from CH station, but did not fill out the form at all on any days. 

How do I show Sarahs name for all days, but also just Sarahs name when i click the CH filter above the table in the screenshot? 
please let me know if my question is confusing

Thank you!
Sarah

[Anonymous]

sorry forgot to add the screenshot @AllisonKennedy 

[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

[PhilipTreacy]

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