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.
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
Solved! Go to Solution.
@Anonymous Al is also in the ch station right so should display too?
Use ALLSELECTED() instead of ALL():
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
@Anonymous Sarah~
Yay! Glad it's working. This is a fun little example, so thanks for the challenge. 🙂
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
@Anonymous Al is also in the ch station right so should display too?
Use ALLSELECTED() instead of ALL():
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
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 @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
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 @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
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.
@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:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
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 @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
Proud to be a Super User!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |