Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a student table with the following columns:-
The column Last Attendance date has the date time stamp of the student swiped into the building from various gate entry points of the school.Now I want to seggregate the datetime stamp column i.e :Last AttendanceDate into the below column categories:-
1. LastAttendanceDatetimestamp
2. SecondLandAttendanceDatetimestamp
so which ever is the last attendance datetimestamp should be shown in LastAttendanceDatetimestamp and whichever is the second last datetimestamp should be shown inSecondLandAttendanceDatetimestamp.
My input data is as below :-
StudentID | StudentName | LastAttendanceDate | StudentType | enteringGate |
100 | Mary | 02-05-2011 10:45 | Fulltime | Gate1 |
100 | Mary | 02-05-2011 12:45 | Fulltime | Gate3 |
100 | Mary | 02-05-2011 09:45 | Fulltime | Gate4 |
101 | John | 02-05-2011 11:59 | Part Time | Gate1 |
101 | John | 02-05-2011 12:56 | Part Time | Gate3 |
101 | John | 02-05-2011 09:55 | Part Time | Gate3 |
101 | John | 02-05-2011 10:48 | Part Time | Gate2 |
Expected Output:
StudentID | StudentName | LastAttendanceDatetimestamp | SecondLastAttendanceDatetimestamp | StudentType | |
100 | Mary | 02-05-2011 12:45 | 02-05-2011 10:45 | Fulltime | |
101 | John | 02-05-2011 12:56 | 02-05-2011 11:59 | Part Time |
Is it possible to write any DAX to handle this scenario on a runtime? There are more records so it needs to evaluate the above condition and categorize into Last attendancedatetimestamp and second last attendancedate timestamp custom columns.
Please suggest any possibile DAX to handle this?
Kind regards
Sameer
Solved! Go to Solution.
please see the attachment and check if this is what you want
Proud to be a Super User!
you can try this
LastAttendanceDatetimestamp = max('Table'[LastAttendanceDate])
SecondLast = MAXX(FILTER('Table','Table'[LastAttendanceDate]<max('Table'[LastAttendanceDate])),'Table'[LastAttendanceDate])
Proud to be a Super User!
Hi @ryan_mayu so incase I have to show the third last attendance date timestamp as well .Can i modify your measure like this?
ThirdLastAttendanceDatetimestamp = MAXX(FILTER(students,[LastAttendanceDatetimestamp]<MAXX(FILTER(students,'students'[LastAttendanceDate]<max('students'[LastAttendanceDate])),'students'[LastAttendanceDate])),[LastAttendanceDatetimestamp])
please kindly see the attachment below
Proud to be a Super User!
Thankyou that was really helpful.
I have a last query, in my data i have two additional fields also added i.e location and desc which signifies the gates from which the student have swiped and entered the school and its description.
I want to also categorize these location and desc columns based on the Last attendancedate timestamp and second last attendancedate timestamp. Please suggest
I have added my pbix file with the datasource included.
my dataource input excel:
Expected output should be something like below:-
could you please suggest how can i add the location and desc as shown above which corresponds to the last attendance dates and second last attendance dates.What changes should i make to the measures you suggested.
Please provide your suggestions and thankyou for all the inputs.It was really helpful
Kind regards
sameer
please see the attachment below
Proud to be a Super User!
Hi,
When I used the same concept in my original dataset , i could get Last attendance date location but not secondLastAttendancedate location.It showed as blank/null. Can we do something like search the second Last attended date against the Last Attended Date column and when there is a match against the location, add the location to the new column secondLastAttendeddate location?
since the second location just fetched null values where as in database we have values and it is showing in the transformation tab too. Rest are all coming fine.
Is there any other measure we can use like lookup or so so search the match and find and update th enew column with location.
since we now have '1' value second Last attendance date so it will do a search in 2 Last attendance date and fetch the location Gate1 in step 3 and update it in the new second Last attendance date column. I was just thinking if such an approach is possible or any other way it can be handled?
Because due to some unknown reasons the second Last attendance date location is not working and it is showing no values.
Please suggest incase we can use any such measures or any other approach.As this is the point which is not coming ,rest all worked really like charm following your suggested approach.
and thankyou for all your inputs until now.It really dot me exposed to new ways in DAX in which I am little new
KInd regards
Sameer
please see the attachment and check if this is what you want
Proud to be a Super User!
Hey @ryan_mayu @amitchandak Thankyou for all your suggestions and yes it worked except the second location on,one of my recent thread has the solution using TOP1 but thankyou again for making me learn new concepts
Hi @ryan_mayu I added a flag column namely 1 and 2. Flag 1 denotes the student with lastAttendancedate and its corresponding location and desc and Flag 2 denotes the SecondLast AttendanceDate and corresponding location and desc.
Can we add any measure to get : lastAttendancedate ,lastAttendancedatelocation and lastAttendancedatedesc and Flag 2 denotes the SecondLast AttendanceDate and SecondLast AttendanceDatecorrespondinglocation and SecondLast AttendanceDatedesc based on the flag value column.
If it was a import table then I could have used conditional column concept and it would have worked but in ourcase we are using Direct query from SQL so conditional column transformation wont support so we need a DAX measure to evaluate it.My original dataource is SQL
I added my new input data source as below link:-
current dataset looks like this with an additional flag column
Could you please suggest any measure or DAX to handle this or how can we modify the above DAX which you suggested
please see the attachment below.
Proud to be a Super User!
Thankyou this was a cool method
you are welcome
Proud to be a Super User!
@deb_power123 , Try this measure
Measure =
VAR __id = MAX ('Table'[StudentID] )
VAR __date = CALCULATE ( MAX('Table'[LastAttendanceDate] ), ALLSELECTED ('Table' ), 'Table'[StudentID] = __id )
CALCULATE ( MAX ('Table'[LastAttendanceDate] ), VALUES ('Table'[StudentID] ),'Table'[StudentID] = __id,'Table'[LastAttendanceDate] < __date )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |