Forum Discussion

carlochecchia's avatar
carlochecchia
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Virtual Tables | Missing Employee

Hi Everyone, I have a merged dataset that combines several forms that my coworkers filled out a regarding the time they started and finished their shifts. I have set up the data refresh so it conti...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    Who forgot to fill out the form? : =
    VAR _lastdate =
    CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS ( Data[Name] ) )
    VAR _previousworkingdate =
    CALCULATE (
    MAX ( Dates[Date] ),
    FILTER (
    Dates,
    Dates[Date] < _lastdate
    && NOT ( Dates[Day of Week] IN { 0, 6 } )
    )
    )
    VAR _employeeslastdatelist =
    CALCULATETABLE (
    VALUES ( Data[Name] ),
    FILTER ( RELATEDTABLE ( Data ), Data[Date] = _lastdate )
    )
    VAR _employeespreviousworkingdatelist =
    CALCULATETABLE (
    VALUES ( Data[Name] ),
    FILTER ( RELATEDTABLE ( Data ), Data[Date] = _previousworkingdate )
    )
    VAR _fillintheformeveryworkingday =
    INTERSECT ( _employeeslastdatelist, _employeespreviousworkingdatelist )
    RETURN
    IF (
    HASONEVALUE ( Names[Name] ),
    IF (
    COUNTROWS ( _fillintheformeveryworkingday ) = 1,
    "fill in on last date",
    "forget to fill in"
    )
    )
     
     
     
     
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

 

Who forgot to fill out the form? : =
VAR _lastdate =
CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS ( Data[Name] ) )
VAR _previousworkingdate =
CALCULATE (
MAX ( Dates[Date] ),
FILTER (
Dates,
Dates[Date] < _lastdate
&& NOT ( Dates[Day of Week] IN { 0, 6 } )
)
)
VAR _employeeslastdatelist =
CALCULATETABLE (
VALUES ( Data[Name] ),
FILTER ( RELATEDTABLE ( Data ), Data[Date] = _lastdate )
)
VAR _employeespreviousworkingdatelist =
CALCULATETABLE (
VALUES ( Data[Name] ),
FILTER ( RELATEDTABLE ( Data ), Data[Date] = _previousworkingdate )
)
VAR _fillintheformeveryworkingday =
INTERSECT ( _employeeslastdatelist, _employeespreviousworkingdatelist )
RETURN
IF (
HASONEVALUE ( Names[Name] ),
IF (
COUNTROWS ( _fillintheformeveryworkingday ) = 1,
"fill in on last date",
"forget to fill in"
)
)
 
 
 
 
carlochecchia's avatar
carlochecchia
Icon for Advocate I rankAdvocate I
5 years ago

Hi Jihwan_Kim, one again thank you for the quick response.

Is there a way that I can show a list of only the people who "Forgot to fill in"? Instead of showing John Maire and Paul, show only Maire..
The way it's set up now, it shows a list of all the coworkers who have ever appeared in the list.


The issue  is I have some coworkers who have filled out this forms before but left the company over a month ago. I don't want to see that person's name in the list, since it will always show that he "Forgot to fill in" since he no longer works with us.

Thats why I thought of comparing only with the ones who filled in the forms the day before vs who filled in today(). Would it be possible?