Power BI as a Behavioral Analytics Engine: Modeling Time, Order, and State

[techies]

Many discussions about Power BI tend to focus solely on dashboards.
Counts. Percentages. Trends.
Dashboards, however, are only the output layer.
The strength of Power BI lies in its semantic model, which organizes and consolidates data behavior.
Lesson streaks offer a method for understanding this distinction.
Not because streaks are complex, but because they collapse immediately when taking modeling shortcuts.

This article reframes Power BI for what it becomes at scale:
A behavioral analytics engine, not a reporting tool.

Behavioral Analytics Starts With Grain, Not Visuals
Most failed Power BI models share a common root cause: the incorrect grain.
Typical dashboard grain: User × Lesson
Behavioral grain: User × Lesson × Activity × Time
This difference is structural, not cosmetic.

Why it matters:
1. Aggregation destroys order
2. Order defines behavior
3. Once data gets aggregated, it becomes impossible to reconstruct the original behavior.

A dashboard can tell you how many activities a user completes.
A behavioral model tells you whether progress actually occurred as designed.

Streaks only exist before aggregation.

Observed vs Expected Behavior — The Semantic Split

Behavior has no meaning without expectation.
Observed Behavior (What Actually Happened)
Observed behavior recorded as immutable events ensures accuracy and accountability.
User X completed Activity Y at Time T.
The LMS fact tables, like quiz and quiz_attempts, track learner attempts. The query filters completed attempts, joins the quiz dimension, and produces a normalized dataset of completions.

let
    Source = dfqn_quiz_attempts,
     #"Filtered Rows" =
        Table.SelectRows(
            Source,
            each ([state] = "finished")
        ),

    #"Merged Quiz Dim" =
        Table.NestedJoin(
            #"Filtered Rows",
            {"quiz"},
            #"dfqn_quiz",
            {"id"},
            "quiz_dim",
            JoinKind.Inner
        ),

    #"Expanded Quiz" =
        Table.ExpandTableColumn(
            #"Merged Quiz Dim",
            "quiz_dim",
            {"course", "name"},
            {"courseid", "activity_name"}
        ),

    #"Selected Columns" =
        Table.SelectColumns(
            #"Expanded Quiz",
            {
                "userid",
                "courseid",
                "quiz",
                "activity_name",
                "timefinish"
            }
        ),

    #"Renamed Columns" =
        Table.RenameColumns(
            #"Selected Columns",
            {
                {"quiz", "activity_id"},
                {"timefinish", "completion_time"}
            }
        ),

    #"Added Activity Type" =
        Table.AddColumn(
            #"Renamed Columns",
            "activity_type",
            each "quiz",
            type text
        )

in
    #"Added Activity Type"

This query represents the Events fact table as evt_quiz.

The same pattern is consistently applied across other LMS activities to produce a unified dataset. For example: evt_hvp

- One row per completion
- No aggregation
- No interpretation
Notice what is deliberately not done:
- No grouping
- No counts
- No streak logic

Tables like evt_quiz and evt_hvp record only facts, not interpretations.

Expected Behavior (Business Intent as Data)

Expected behavior is defined, not calculated. The lesson sequence reflects intent with activities modeled to improve engagement.

let
    Source =
        #table(
            {"courseid", "lesson_id", "lesson_name", "sequence_no", "activity_type", "activity_id"},
            {
                {10, "L01", "Lesson A", 1, "quiz", 3},
                {10, "L01", "Lesson A", 2, "quiz", 4},
                {10, "L01", "Lesson A", 3, "quiz", 2},
                {10, "L01", "Lesson A", 4, "hvp", 1}
            }
        ),
    #"Added Custom" = Table.AddColumn(Source, "activity_key", each [activity_type] & "-" & Text.From([activity_id]))
in
    #"Added Custom"

The LessonSequence table does not include any analytical logic; it acts as a semantic contract.

Append Is a Semantic Decision, Not a Technical One

Quizzes and H5P activities are technically different.
Behaviorally, they are identical.
Appending declares this equivalence:

let
    Source = Table.Combine({evt_hvp, evt_quiz}),
    #"Added Custom" = Table.AddColumn(Source, "activity_key", each [activity_type] & "-" & Text.From([activity_id]))
in
    #"Added Custom"

At this moment, Power BI stops being a collection of tables and becomes an event analytics platform.

Completion Order Is Structural — Not a Measure

Many models fail here. Completion order is deterministic, must survive filtering, and avoid recalibration for each visual. Thus, it belongs in Power Query at refresh time, not in DAX.

let
    
    // SOURCE
    
    Source = FactActivityEvents,

  
    // UNIX → DATETIME
    
    AddCompletionDateTime =
        Table.AddColumn(
            Source,
            "completion_datetime",
            each #datetime(1970, 1, 1, 0, 0, 0)
                + #duration(0, 0, 0, [completion_time]),
            type datetime
        ),

    
    // MERGE LESSON SEQUENCE
 
    MergeLesson =
        Table.NestedJoin(
            AddCompletionDateTime,
            {"activity_key"},
            LessonSequence,
            {"activity_key"},
            "lesson_dim",
            JoinKind.Inner
        ),

  
    // EXPAND LESSON FIELDS
   
    ExpandLesson =
        Table.ExpandTableColumn(
            MergeLesson,
            "lesson_dim",
            {"lesson_id", "lesson_name", "sequence_no"},
            {"lesson_id", "lesson_name", "sequence_no"}
        ),

    
    // SORT (CRITICAL)
    
    SortRows =
        Table.Sort(
            ExpandLesson,
            {
                {"userid", Order.Ascending},
                {"lesson_id", Order.Ascending},
                {"completion_datetime", Order.Ascending}
            }
        ),

    
    // GROUP BY USER + LESSON
    
    Grouped =
        Table.Group(
            SortRows,
            {"userid", "lesson_id"},
            {
                {
                    "events",
                    (t as table) =>
                        let
                            // Add completion order
                            AddOrder =
                                Table.AddIndexColumn(
                                    t,
                                    "completion_order",
                                    1,
                                    1,
                                    Int64.Type
                                ),

                            // Is In Sequence
                            AddIsInSequence =
                                Table.AddColumn(
                                    AddOrder,
                                    "is_in_sequence",
                                    each if [completion_order] = [sequence_no] then 1 else 0,
                                    Int64.Type
                                ),

                            // Running Streak (cumulative sum)
                            AddRunningStreak =
                                Table.AddColumn(
                                    AddIsInSequence,
                                    "running_streak",
                                    each
                                        List.Sum(
                                            List.FirstN(
                                                AddIsInSequence[is_in_sequence],
                                                [completion_order]
                                            )
                                        ),
                                    Int64.Type
                                )
                        in
                            AddRunningStreak,
                    type table
                }
            }
        ),

    
    // EXPAND BACK TO FLAT TABLE
    
    ExpandFinal =
        Table.ExpandTableColumn(
            Grouped,
            "events",
            {
                "lesson_name",
                "activity_type",
                "activity_id",
                "activity_name",
                "sequence_no",
                "completion_time",
                "completion_datetime",
                "completion_order",
                "is_in_sequence",
                "running_streak"
            }
        )
in
    ExpandFinal

Events are indexed for each user and lesson, creating a reliable behavioral timeline in the FactLessonEvents table. This method mirrors SQL window functions while ensuring refresh safety and semantic accuracy.

Aligning Events to Expectation

Behavior only exists when compared to intent.
A composite key bridges the two worlds:
activity_key = activity_type & "-" & activity_id

Now every event can answer:
Was this activity expected at this point? Or is this a deviation?

Without this alignment, streaks are impossible.

Modeling Streaks as Behavioral State

Streaks are not metrics. They are states.

Computed at refresh time:
- completion_order
- is_in_sequence
- running_streak

is_in_sequence =if [completion_order] = [sequence_no] then 1 else 0

Unmet expectations break the streaks.
No DAX workaround can repair a broken semantic chain.

Where DAX Belongs (And Where It Does Not)

At this stage, the model understands:
- What occurred
- The sequence of events
- The expected outcome

Key distinction:

- Power Query dictates behavior.
- DAX summarizes behavior.

If DAX has to reconstruct sequences, the model is flawed.

Final DAX Measures 

Assuming the final table FactLessonEvents:

Current Streak= MAX ( FactLessonEvents[running_streak] )

First Deviation Activity = 
VAR BreakTime =
    CALCULATE (
        MIN ( FactLessonEvents[completion_datetime] ),
        FactLessonEvents[is_in_sequence] = 0
    )
RETURN
MAXX (
    TOPN (
        1,
        FILTER (
            FactLessonEvents,
            FactLessonEvents[is_in_sequence] = 0
                && FactLessonEvents[completion_datetime] = BreakTime
        ),
        FactLessonEvents[completion_datetime], ASC
    ),
    FactLessonEvents[activity_name]
)

Last Completed Activity = 
VAR LastTime =
    MAX ( FactLessonEvents[completion_datetime] )
RETURN
CALCULATE (
    SELECTEDVALUE ( FactLessonEvents[activity_name] ),
    FactLessonEvents[completion_datetime] = LastTime
)

That’s not a limitation; it’s evidence that the model is correct.

Why This Pattern Scales

Lesson streaks are just one example.
The same semantic pattern applies to:
- Onboarding journeys
- Feature adoption flows
- Financial progressions
- Health programs
- Gaming systems

Anywhere order matters, this model holds.

Behavioral models treat time as structure.
Lesson streaks work not because of clever calculations but because the model respects:
- Event grain
- Order
- Expectation State

When analytics respects sequence, it stops being descriptive and starts being behavioral.

Here is the PBIX file for code and modeling reference only.

[krishnakanth240]

This is a good perspective @techies 
Clearly separates dashboard output vs semantic behavior modeling, and stress that grain + order + expectation must be designed before any DAX.

'Streaks collapse when you shortcut the model' point is true and valuable info for anyone building scalable Power BI models.