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
Icon for Super User rankSuper 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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

 

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's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

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

        sorry forgot to add the screenshot AllisonKennedy