Power BI中DAX计算次近登录时间异常问题求助

Let's break down why your current measure is misbehaving and get it sorted out. The core issue here is how your date filter is restricting the context of the TOPN function.

What's Wrong with the Original Measure? #

Your current 2ndToLast measure uses a FILTER on the Date table to lock into the selected date. That means when you run TOPN(2, 'AppAccessLog', 'AppAccessLog'[LoginTime]), it only looks at login records from the selected day. If the user's second-most recent login was on the previous day, this TOPN will only grab the single login from the selected date (or near-duplicates if there are multiple same-day logins), leading to the incorrect result you saw.

Solution 1: Fetch All Relevant Logins for the User #

This approach pulls all of the user's logins up to the selected date, then picks the top 2 most recent entries:

2ndToLast = 
VAR SelectedDate = SELECTEDVALUE('DateSelector'[DateId], MAX('DateSelector'[DateId]))
VAR SelectedDateEnd = MAX('Date'[Date]) + TIME(23,59,59) -- Capture end of the selected day
VAR UserAllLogins = 
    CALCULATETABLE(
        'AppAccessLog',
        -- Include all logins up to the end of the selected date
        'AppAccessLog'[LoginTime] <= SelectedDateEnd,
        -- Preserve current user context (adjust column name if your user ID has a different label)
        ALLEXCEPT('AppAccessLog', 'AppAccessLog'[UserId])
    )
VAR Top2RecentLogins = TOPN(2, UserAllLogins, 'AppAccessLog'[LoginTime], DESC) -- Sort newest to oldest
RETURN
    -- Only return a value if there are at least 2 logins to avoid errors
    IF(COUNTROWS(Top2RecentLogins) >= 2, MINX(Top2RecentLogins, 'AppAccessLog'[LoginTime]), BLANK())

Solution 2: Target the Second-Most Recent Login Directly #

This method first finds the latest login on the selected date, then grabs the most recent login that happened before that timestamp:

2ndToLast = 
VAR SelectedDate = SELECTEDVALUE('DateSelector'[DateId], MAX('DateSelector'[DateId]))
VAR UserLatestLoginOnSelectedDay = 
    CALCULATE(
        MAX('AppAccessLog'[LoginTime]),
        FILTER('Date', 'Date'[DateId] = SelectedDate)
    )
RETURN
    CALCULATE(
        MAX('AppAccessLog'[LoginTime]),
        -- Find the newest login earlier than the latest on the selected day
        'AppAccessLog'[LoginTime] < UserLatestLoginOnSelectedDay,
        -- Keep only the current user's logins
        ALLEXCEPT('AppAccessLog', 'AppAccessLog'[UserId])
    )

How These Fixes Work #

• Both solutions preserve the user context (so results are per-user) while removing the restrictive date filter that blocked access to previous day's logins.
• For your test case with user X100:
  • When both logins are on 2020-02-27, either measure will correctly pick the earlier of the two same-day logins.
  • When the second login is on 2020-02-26, the measures will look beyond the selected day to find that prior login, returning the correct 2020-02-26 17:43:45.900 value.

Quick Notes #

• Ensure your AppAccessLog table has a UserId column (or equivalent) to distinguish between users. Adjust the ALLEXCEPT argument if your column name differs.
• If your dataset is large, adding a reasonable date range (like restricting to the last 30 days) in UserAllLogins can help improve performance.

内容的提问来源于stack exchange，提问作者James Khan