Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    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], ", ")
  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Pragati11
        Super User
        5 years ago

        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