Forum Discussion
Filtering Dates
| For each User ID, I need to identify the rows where LogInDates occurs on the same day. After that I need to identify the closest LogOutDates that occurs on the same day or after. There can be only two dates in a pair but there can be multiple pairs for each UserId. The LogOutDate and is not dependant on the LoginDate. How do I do this in Dax? |
| User Id | LogInDate | LogOutDate | Expected |
| A | 12/29/2013 | 1/1/2014 | |
| A | 12/30/2014 | 1/1/2015 | x |
| A | 12/29/2014 | 1/1/2015 | |
| A | 12/30/2014 | 1/1/2016 | x |
| B | 1/30/2015 | 2/1/2015 | x |
| B | 1/30/2015 | 2/1/2015 | x |
| B | 1/30/2015 | 4/1/2015 | |
| B | 1/30/2015 | 3/1/2015 | |
| C | 4/26/2016 | 5/1/2016 | |
| C | 5/27/2016 | 6/1/2016 | x |
| C | 5/27/2016 | 6/1/2016 | x |
| C | 5/28/2016 | 6/2/2016 | |
| C | 5/29/2016 | 6/3/2016 |
3 Replies
To achieve this in DAX (Data Analysis Expressions), you can create a calculated column that flags the rows where LogInDate and LogOutDate occur on the same day and then identify the closest LogOutDate that occurs on the same day or after the LogInDate. Below is the DAX code to implement this logic:
PairFlag =
VAR CurrentUserId = 'YourTable'[User Id]
VAR CurrentLogInDate = 'YourTable'[LogInDate]
VAR SameDayLogOut =
FILTER(
'YourTable',
'YourTable'[User Id] = CurrentUserId &&
'YourTable'[LogOutDate] >= CurrentLogInDate &&
DATEDIFF('YourTable'[LogInDate], CurrentLogInDate, DAY) = 0
)
VAR ClosestLogOutDate =
MINX(
FILTER(
SameDayLogOut,
'YourTable'[LogOutDate] >= CurrentLogInDate
),
'YourTable'[LogOutDate]
)
RETURN
IF(
'YourTable'[LogOutDate] = ClosestLogOutDate,
"x",
BLANK()
)Replace 'YourTable' with the actual name of your table in your Power BI model. This DAX code creates a calculated column called PairFlag which checks for each row if there exists a LogOutDate on the same day as the LogInDate, and then finds the closest LogOutDate on the same day or after the LogInDate. If the LogOutDate is the closest one, it flags it with "x", otherwise, it remains blank.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Simple enough
My appologies. I don't think what I explained was clear. What I am wanting is For each User ID, I want to determine if the LogoutDate is on/after the current row. If it is then flag those two rows then go to the next unflagged row and perform the same analysis.
