Power BI - 2 Part Matching Values / Definition

[muom]

I'm struggling to know exactly what to ask for this question. I have a data set with unique enrollment identifiers, showing employment status someone's entry and exit in our program as well as occasional updates during the program.

What I am trying to do is find a measure that I think will be three-parts.

1. Find matching values within the Unique Enrollment Identifier

2. Find if the matching values have an Entry and Exit

3. Compare if the Employment Status has changed from Entry to Exit.

The end goal is to create a visual showing changes in employment status from entry to exit.

I've included a screenshot example of the data for more clairty. Would appreciate guidance on the most effective formualas to get there!

[v-hashadapu]

Hi @muom , I wanted to check with you and see if the provided information was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!

[Nasif_Azam · Accepted solution]

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