DAX to determine the date column with null value and display it as per Date slicer selection

[deb_power123]

Hi Everyone,

I have a typical scenario as below.

I have a student table and it contains four columns as below :-

1.StudentID
2. StudentName
3.LastAttendanceDate
4.StudentType

Now there are some null values in the date column LastAttendanceDate.Is it possible to use a date slicer to show these values of the students who have LastAttendanceDate column value as null? In simple words: Say you are a student who went to a school on Monday,Tuesday and Friday and you were absent on Wednesday and Thursday so here wednesday and Thursday are the days where you were absent in the week and we need to display these records in the table visualization . 

My excel Input data:-

StudentID	StudentName	LastAttendanceDate	StudentType
100	Mary	02-05-2011 10:45	Fulltime
100	Mary		Fulltime
100	Mary	04-05-2011 12:45	Fulltime
100	Mary	06-05-2011 15:45	Fulltime
100	Mary		Fulltime
100	Mary	08-05-2011 19:45	Fulltime
100	Mary	09-05-2011 12:45	Fulltime
101	John	02-05-2011 10:45	Part Time
101	John	03-05-2011 11:23	Part Time
101	John	04-05-2011 10:45	Part Time
101	John	06-05-2011 15:49	Part Time
101	John		Part Time
101	John	08-05-2011 19:45	Part Time
101	John	09-05-2011 12:45	Part Time

so here I need to dynamically find in the week/month range or any dynamic date range say  from date range 02-05-2011 and 08-05-2011 or 02-05-2011 and 09-05-2011 or even 06-05-2011 and 09-05-2011,  the students who were absent and show it in my table visualization.

Can anyone provide an approach or any helpful DAX? Appreciate for all the help

My present visualization looks like this : I want to show the students who were absent in the given timerange as selected in the dateslicer.

so if I slide the date slicer as per minimum and maximum ranges, it should show all the rows of students who were absent or with null values for Last Attendance Date column in those time range.

Kind regards

Sameer

[MFelix · Accepted solution]

Hi @deb_power123 ,

I assume you calendar is connected with the last date, also that the weekends are not school days so I have added the following measure:

Absent =
VAR temp_table_Names =
    FILTER (
        SELECTCOLUMNS ( ALL ( 'Table'[StudentName] ), "Name", 'Table'[StudentName] ),
        NOT ( [Name] IN VALUES ( 'Table'[StudentName] ) )
    )
VAR result =
    CONCATENATEX ( temp_table_Names, [Name], ", " )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'calendar'[weekday] ) = "weekday", IF ( ISBLANK ( result ), "", result )
    )

This will give result below:

Check PBIx file attach.

[deb_power123]

Thankyou, and I will say this is the best technique you made me learn.I may have some more variations in the data and I will seek your suggestions in those case scenarios.I mostly have the column value in date time instead of date.But thnakyou for your answer, it helped

[deb_power123]

Hi @MFelix 

I tried to replicate the DAX formula you provided in a different set of data  but i am getting both employee names in absent list as per screenshot below:-

Have you made any additional configurations or change apart of the measures? Please find below the link for the .pbix file .

could you please suggest where I am making a mistake in the below file?

my pbix file 

Kind regards

Sameer

[MFelix]

Hi @deb_power123 ,

The relationship between the calendar table and the attendance table needs to be done on date level so you need to format both columns of the relationship has date and then make the relationship.

Your file has the data connected to an excel spreadsheet so I cannot change it directly, but what you need to do is to create a date on column on your attendance table and format the calendar table as da date column, then everything should work properly.

[deb_power123]

Thankyou for the reply, its very strange that I did exactly the same way as you suggested.Converted the type to date time but the results of absent is not coming correct.Below are the screenshots:-

created custom date as you created and made it date and it is duplicate of LastAttendancedate column

Made the type as date  for calendar table date column

Related calendar to student table with Date and custom column as shown below:-

All the values are showing as both employees are absent:

DAX measure used:-

But for your pbix file it is showing correctly.is there something I did incorrect or missing.Please provide your opinion

kind regards

sameer

[MFelix]

Hi @deb_power123 ,

In the new column you create don't do the base of the last attendance and then formatting has date create a date column you can use one of the following code:

Date = Date(YEAR(Table[LastAttendace]), MONHT(Table[LastAttendace]), DAY(Table[LastAttendace]))

Date = Table[LastAttendace].[Date]

You can also create this column on the query editor using the syntax:

DateTime.Date ([LastAttendance])

Believe that when making the conversion is still keeping the hour in the background so the calculations are not returning correctly, don't really understand why.

[deb_power123]

Thankyou so much for the guidance.I think I got the approach now.I was really struggling with this.