cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sk15227
Post Patron
Post Patron

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

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@sk15227  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], ", ")

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

13 REPLIES 13
AllisonKennedy
Super User
Super User

@sk15227  Sarah~

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


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@sk15227  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], ", ")

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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

sk15227
Post Patron
Post Patron

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!

Hi @sk15227 ,

 

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

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

Hi @sk15227 ,

 

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

 

This is your STATIC data table:

Pragati11_0-1608045744527.png

 

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

Pragati11_1-1608045798773.png

 

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:

Pragati11_2-1608045870154.png

 

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

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

 

Capture.PNG

 

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.

@sk15227  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.

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

sorry forgot to add the screenshot @AllisonKennedy Capture.PNG

Pragati11
Super User
Super User

Hi @sk15227 ,

 

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

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

PhilipTreacy
Super User
Super User

Hi @sk15227 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!