PowerBI Desktop：构建用户状态类别历史变化时序可视化技术求助

Prerequisites #

• Ensure you have a Date Table in your model, marked as a date table (via Table tools > Mark as date table). This table should cover all dates you need to visualize.
• No direct relationships are required between the Date Table and your state tables—measures will handle date filtering dynamically.

Step 1: Create a Measure to Fetch User State as of a Selected Date #

This measure determines each user’s state on any given date by prioritizing their latest state history entry (if it exists on or before the selected date) and falling back to their current state if no history applies.

User State as of Date = 
VAR SelectedDate = MAX('Date Table'[Date])
VAR CurrentUser = SELECTEDVALUE('User Current State'[UserID])

-- Retrieve all state changes for the user up to the selected date
VAR UserHistory = CALCULATETABLE(
    'State History',
    'State History'[UserID] = CurrentUser,
    'State History'[Change Date] <= SelectedDate
)

-- Get the most recent state change entry
VAR LatestHistoryEntry = TOPN(1, UserHistory, 'State History'[Change Date], DESC)
VAR LatestHistoryState = SELECTCOLUMNS(LatestHistoryEntry, "State", 'State History'[State])

-- Return current state if no history exists, else the latest history state
RETURN
IF(
    ISBLANK(LatestHistoryState),
    SELECTEDVALUE('User Current State'[Current State]),
    LatestHistoryState
)

Step 2: Create a Measure to Count Users by State #

This measure calculates how many users are in each state as of the selected date by iterating over all users and grouping their states.

State User Count = 
VAR SelectedDate = MAX('Date Table'[Date])
VAR UserStateSummary = ADDCOLUMNS(
    ALL('User Current State'[UserID]),
    "UserState",
    -- Reuse state lookup logic for each user
    VAR UserHistory = CALCULATETABLE(
        'State History',
        'State History'[UserID] = 'User Current State'[UserID],
        'State History'[Change Date] <= SelectedDate
    )
    VAR LatestEntry = TOPN(1, UserHistory, 'State History'[Change Date], DESC)
    RETURN IF(ISBLANK(LatestEntry), 'User Current State'[Current State], SELECTCOLUMNS(LatestEntry, "State", 'State History'[State]))
)

-- Count users matching the selected state in the visual
RETURN
COUNTROWS(
    FILTER(
        UserStateSummary,
        [UserState] = SELECTEDVALUE('User Current State'[Current State])
    )
)

Step 3: Build the Timing Visualization #

1. Add a line chart or stacked area chart to your report.
2. Drag Date from your Date Table to the Axis.
3. Drag the State field (from either your User Current State or State History table) to the Legend.
4. Drag the State User Count measure to the Values area.

Troubleshooting Tips #

• Ensure your Date Table includes dates from the earliest state change record to the latest current state date to avoid gaps in visualization.
• Verify UserID values match exactly between the User Current State and State History tables (no data type mismatches or typos).
• For better consistency, create a separate State dimension table and update measure references to use this table’s state column instead of columns from your transactional tables.

内容的提问来源于stack exchange，提问作者Clifford Piehl