Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |