DAX to Filter date column and add it to new columns based on flag values for direct query

[deb_power123]

Hi All,

In my below student table I have the following columns:-

StudentID
StudentName
AttendanceDate
StudentType
location
desc

flag

I need to create calculated column or DAX measure to find the LastAttendanceDate and SecondLastAttendanceDate and LastAttendanceDate and  SecondLastAttendanceDateLocation based on the flag values in the flag column .

Here flag value1 denotes the lastAttendancedate and last attendancelocation  and flagvalue 2 denotes SecondLastAttendanceDate and SecondLastAttendanceLocation row values respectively. 

The solution was simple if we needed to evalaute this using conditional column concept but I am using direct query and it wont help. so any DAX measure can work in my case.

My input datasource excel is as under:-

StudentID	StudentName	AttendanceDate	StudentType	location	desc	flag
100	Mary	02-05-2011 10:45	Fulltime	Gate1	Gate1location	2
100	Mary	02-05-2011 12:45	Fulltime	Gate2	Gate2location	1
100	Mary	02-05-2011 09:45	Fulltime	Gate3	Gate3location
100	Mary	02-05-2011 08:45	Fulltime	Gate3	Gate3location
101	John	02-05-2011 10:59	Part Time	Gate2	Gate2location	2
101	John	02-05-2011 12:56	Part Time	Gate1	Gate1location	1
101	John	02-05-2011 09:55	Part Time	Gate5	Gate5location
101	John	02-05-2011 06:35	Part Time	Gate1	Gate1location
101	John	02-05-2011 10:48	Part Time	Gate3	Gate3location
102	Tom	03-05-2011 09:55	Part Time	Gate3	Gate3location
102	Tom	03-05-2011 10:48	Part Time	Gate1	Gate1location	2
102	Tom	03-05-2011 09:55	Part Time	Gate1	Gate1location
102	Tom	03-05-2011 10:47	Part Time	Gate1	Gate1location
102	Tom	03-05-2011 13:48	Part Time	Gate2	Gate2location	1

Expected output:

Is there a way to achieve this using DAX filter function ?

Please suggest your thoughts .

Kind regards

Sameer

[CNENFRNL · Accepted solution]

@deb_power123 , you might want try any one of solutions, ie PQ solution, DAX solution and Excel formula solution, down below,

PQ solution

DAX solution,

Excel formula solution, our oldie but goodie, and also my favorite,

[deb_power123]

Thankyou @CNENFRNL your suggested approach worked like a charm.You really made me learn a nice concept today.RANK(1) did the trick.Best solution...Thankyou again, it seggregates all required fields perfectly

[deb_power123]

Hi @CNENFRNL  thankyou for your reply but in my requirement I have added a flag column denoting flag 1 as row entry for lastAttendanceDate and flag 2 as row entry for SecondLastAttendanceDate.I added the link of my excel as below.

My input datafile 

My datasource is using Direct Query so I will require to use DAX measure for this.

Could you please suggest any DAX measure to return the same output you provided above but based out of the flag values.

I want to achieve the below output as in screenshot based on the flag values.

Please provide your thoughts.I tried DAX and other approaches of conditional format but it is failing.I am clueless.I am doing it somewhere incorrect, please letme know if you have any inputs based on my data

My input datafile