Power BI - 2 Part Matching Values / Definition

Hey muom ,

You're on the right track with your goal: comparing employment status at Program Entry versus Program Exit for each Unique Enrollment Number. Here's a step-by-step breakdown of how to build this in Power BI using DAX.

Objectives

Final Visual: A visual showing change in employment status from entry to exit:

• No → Yes (got employed)

• Yes → No (lost job)

• Yes → Yes (remained employed)

• No → No (remained unemployed)

1. Create a Summary Table (DAX Calculated Table)

EmploymentStatusChanges =
SELECTCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            VALUES('Table'[Unique Enrollment Number]),
            "EntryStatus", CALCULATE (
                MAX('Table'[Is the client currently employed?_1384]),
                'Table'[At what point is this data being collected?_9227] = "Program Entry"
            ),
            "ExitStatus", CALCULATE (
                MAX('Table'[Is the client currently employed?_1384]),
                'Table'[At what point is this data being collected?_9227] = "Program Exit"
            )
        ),
        NOT ISBLANK([EntryStatus]) && NOT ISBLANK([ExitStatus])
    ),
    "EnrollmentID", 'Table'[Unique Enrollment Number],
    "EntryStatus", [EntryStatus],
    "ExitStatus", [ExitStatus]
)

2. Add a New Column: Status Change

To compare Entry vs Exit and label the change:

StatusChange = 
SWITCH(
    TRUE(),
    [EntryStatus] = "No" && [ExitStatus] = "Yes", "No → Yes (Gained Employment)",
    [EntryStatus] = "Yes" && [ExitStatus] = "No", "Yes → No (Lost Employment)",
    [EntryStatus] = "Yes" && [ExitStatus] = "Yes", "Yes → Yes (Still Employed)",
    [EntryStatus] = "No" && [ExitStatus] = "No", "No → No (Still Unemployed)",
    "Unknown"
)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam