Filter by date and employee

[smatesic]

I have one powerBI report which deals with working hours of the employees in company. I made it some time ago and now I need to show some extra information. 

Report is fairly simple. It has DimTable calculated in DAX, WorkLog table with date, start, end, employee and log columns (information about work hours) and Employee table with employeeID, employeeName and active columns. 

DimTable is connected to WorkLog by date column (two way direction) and Employee table is conected to WorkLog table by employee column (one way). 

I also calculated IsWorkingDay column in DimTable which returns true/false - true if that day is not weekend or holiday. 

I also added one more column in DimTable which is based on IsWorkingDay. That column, currently named AllWorkingDays returns 1 if it's working day, or 0 if it's not.

What I need to see now is: did employee worked on day when he was supposed to work 

In dashboard, I added that AllWorkingDays column and filtered it by employee. What it does, logicaly, it goes frome Employee table, trough WorkLog into DimTable and it shows results already filtered by WorkLog. What I get are non-holiday and non-weekends dates for all the logs that exist. When someone is sick or if employee gets day off, he doesn't put anything in WorkLog. I need to see those dates that don't have logs. 

I don't know if I was clear enough...

[v-sihou-msft]

@smatesic

In this scenario, you can create an ‘Attendance’ column in WorkLog table with below formula. Then you should be able to get the expected results.

Attendance = 
IF (
    WorkLog[Log] = BLANK ()
        && RELATED ( DimTable[AllWorkingDays] ) = 1,
    "Absence",
    "On Duty"
)

Regards,

[smatesic]

That would probably work, but I don't have empty rows in date and log columns i WorkLog table. Source of my data is sharepoint list. There every employee logs his hours. When someone doesn't come to work, he simply doesn't put anything in that sharpoint list. 

For example. These are log dates for one employee. From 22nd to 28th there are no logs and I don't have blank so I can't use them in my formula. 

I need to see which are these dates that are not listed in WorkLog. I added calculated column in DimTable in which I showed all the dates that are not saturday, sunday or holiday. It looks like this:

IsWorkingDay = IF (NOT(DimTable[WeekName]= "Saturday" || (DimTable[WeekName]= "Sunday")) && COUNTX(RELATEDTABLE(Holidays);1)<1;1;0)

So now I need another calculated columns that will show me all the dates in WorkLog that are not there, and at the same time are not weekend or holiday. 
In the example above those dates would be from 22nd to 24th and then from 27th to 29th, total of 6 days

[v-sihou-msft]

@smatesic

If there is no log when one employee doesn’t come to work, we can first cross join tables of “DimTable” and “Employee” with following formula. A new relationship between Table (EmployeeID) and Employee (EmployeeID) should be created.

Table =
CROSSJOIN ( DimTable, Employee )

Then we can create a column in Table with following formula.

State =
VAR LogRecord =
    CALCULATE (
        COUNTROWS ( WorkLog ),
        FILTER (
            WorkLog,
            'Table'[Date] = WorkLog[Date]
                && 'Table'[EmployeeID] = WorkLog[Employee]
        )
    )
RETURN
    (
        IF (
            LogRecord = 0
                && 'Table'[IsWorkingDay] = TRUE (),
            "Absence",
            IF ( LogRecord <> 0 && 'Table'[IsWorkingDay] = TRUE (), "On Duty", "Holiday" )
        )
)

Regards,

[smatesic]

This formula works, but I'm far from where I need to be. 

I modified the formula so it fits my columns and data. It looks like this now:

OnPosition = VAR TitleRecord = 
    CALCULATE (
        COUNTROWS ( 'Work Log' );
        FILTER (
            'Work Log';
            'Table'[Date] = 'Work Log'[Date] && 'Table'[Employee] = 'Work Log'[Employee]
        )
    )
RETURN 
	(
		IF( TitleRecord = 0 && 'Table'[IsWorkingDay] = TRUE ();
            "Absence";
            IF ( TitleRecord <> 0 && 'Table'[IsWorkingDay] = TRUE (); "On Duty"; "NonWorkingDay" )
        )
)

I changed your 'Holiday' to NonWorkingDay because Holiday returned all holidays and weekends together. I don't need that. I need formula to say weather it was working day, holiday or weekend and if it was weekend if someone worked that day. There were many situation when employee worked on saturday or sunday and then got the day off because of it. 

Basicaly, I need to see when and how much employee worked and do some calculations based on that. 

Here are my tables now:

Can it maybe be done without using extra table? Now, I can't use nothing from other tables as filter. 

Is it maybe possible to somehow add in query all the dates that are missing in work log? Formula that would do something like - go to work log table, check all the dates in logs, then compare them to dates in DimTable which has every date since 2010, if certain date is missing write abscence.  Otherwise, set as 'working'...

Thank you for your help so far

[smatesic]

I was not able to solve this yet. Any ideas?