ABSENT NAME LIST

[rajasekar_o]

i have employee master table and attendance table
If an employee does not have an "IN" entry in the inout column on the selected date, then display the list of names for absent employees

 

[speedramps · Accepted solution]

Download this solution ...
Click here to download PBIX from OneDrive 

How it works ...

Create a calendar table  from start to end date with no  missing dates.

Add a column to indicate which are working day and which are holidays 

otherwise everyone will be reported absent on those days

Create relationships exactly like this diagram

Create a measure whic returns TRUE if there are no IN rows for the name and date for working days.

So excludes holidays and weekends

Absent on work day = 
IF(SELECTEDVALUE('Calendar'[IsWorkingDay]),
CALCULATE(
    ISEMPTY(Logfile),
    Logfile[Status] = "IN"
))

Use a matric visual

with Rows = Staff[Name]

with Colum = Calendar[Date]

with value = [Absent on work day]

take care to use correct table names andnot the logfile, otherwise it wont work

Thanks for the clear description of the problem with example data.

Next time please provide the data as text and not a picture, so we can import the data.

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[v-weiyan1-msft · Accepted solution]

Hi @rajasekar_o ,

Based on your description,
here is the data I created from the sample you provided.
Employee table:

Attendance table:

Please try the following steps:
1.You can create a Calcualted table.

Table = VALUES('Attendance'[DATE])

2. Use the following code to create a Measure.

Measure = 
VAR SelectedDate = SELECTEDVALUE ( 'Table'[DATE] )
RETURN
IF( SELECTEDVALUE('Employee'[ID]) IN 
                CALCULATETABLE (
                VALUES ( 'Attendance'[ID] ),
                'Attendance'[Date] = SelectedDate,
                'Attendance'[IN OUT] = "IN" ),
                0,
                1)

3. Select your visual object, put the measure in the “Filters on this visual" section, and filter it by “Measure is 1".

The field of the slicer is from Table. When you select "3/20/2023" in the slicer, Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

I provided a smimilare solution days ago but @rajasekar_o has not responded.

Please be polite and click "accept as solution" and thumbs up.

Rememnber we are unpaid volunteers and you may want free expert help again.

[speedramps]

@rajasekar_o please click the [accept as solution] and the thumbs up button. 

I have provide a example, screenshots and detailed explanation.

It does exactly what you asked for.

Thank you.

[speedramps · Accepted solution]

Download this solution ...
Click here to download PBIX from OneDrive 

How it works ...

Create a calendar table  from start to end date with no  missing dates.

Add a column to indicate which are working day and which are holidays 

otherwise everyone will be reported absent on those days

Create relationships exactly like this diagram

Create a measure whic returns TRUE if there are no IN rows for the name and date for working days.

So excludes holidays and weekends

Absent on work day = 
IF(SELECTEDVALUE('Calendar'[IsWorkingDay]),
CALCULATE(
    ISEMPTY(Logfile),
    Logfile[Status] = "IN"
))

Use a matric visual

with Rows = Staff[Name]

with Colum = Calendar[Date]

with value = [Absent on work day]

take care to use correct table names andnot the logfile, otherwise it wont work

Thanks for the clear description of the problem with example data.

Next time please provide the data as text and not a picture, so we can import the data.

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[rajasekar_o]

ok

[speedramps]

please read my earlier post which has link