Preview. This page currently shows the analysis skeleton. The full worked walkthrough — sample dataset, connected steps, runnable code, interpretation — is being built.

HR / People Analytics · how-to

Flight Risk — predict which employees will resign — in DAX (Power BI)

A turnover-prediction analysis. Every step below is the same analysis rendered for DAX (Power BI) — grounded in the source, honest where DAX (Power BI) can't do a step cleanly.

Same analysis, your tool

The problem

Every time a valued employee resigns, your organization absorbs a hidden bill: the cost of recruiting a replacement, the weeks of lost productivity while the seat sits empty, and the slow drain on morale as remaining team members pick up the slack. HR leaders and line managers feel this most sharply, because they are the ones who learn about a departure only after the resignation letter lands — far too late to do anything about it.

The question on the table is simple to ask and hard to answer: which of the people you have right now are most likely to walk out the door next? If you could see that coming, you could spend your limited retention budget and management attention on the handful of people who actually need it, instead of reacting one exit at a time.

The analysis — and why it fits

You are trying to sort each current employee into one of two outcomes — likely to leave, or likely to stay — and to attach a number to that judgment so you can rank people by how much attention they need. A method built for this shape takes a set of measurable employee attributes and estimates the probability of the leave-or-stay outcome, producing a score between 0 and 1 for every person. That probability is exactly what a manager needs: not just a yes-or-no label, but a sense of how urgent each case is.

The main alternative is a rule-based tree that splits people into groups with a series of if-then questions. That approach is easy to read, but it hands you buckets rather than a smooth, comparable likelihood for each individual, which makes ranking people against one another awkward — so here you want the probability estimate instead.

Why DAX (Power BI)

DAX inside Power BI is one of the most widely deployed analytics engines in business because it sits directly on top of the data model your organization already uses for reporting. Once your HR data lands in a Power BI table, DAX measures recalculate instantly across slicers and filters, so the same resignation model you build here becomes a live dashboard tile the moment you publish it. For this analysis specifically, DAX gives you fast columnar aggregation for correlations, row-level arithmetic through calculated columns for scoring every employee, and iterators like SUMX and AVERAGEX that let you assemble the pieces of a logistic score without leaving the report layer.

The honest weakness is that DAX has no native maximum-likelihood optimizer: it cannot fit a logistic regression by itself the way R's glm or Python's statsmodels can. You can compute correlations, apply a logistic formula, and score employees natively, but the coefficient-fitting step must either be pre-estimated externally or approximated, and that is where this walkthrough diverges from a pure DAX path.

Who this is for. You work in HR, people analytics, or a management role where you own retention outcomes and want to move from reacting to resignations to anticipating them. You are comfortable with basic spreadsheets and summary statistics, but you do not need any prior modeling experience; the accompanying dataset download gives you every column you need to follow along. Plan on about 45 minutes.

Where we’re going

By the end, you will have a ranked flight-risk table: one row per employee, each carrying a resignation probability between 0 and 1 and a clear flag marking anyone above the halfway mark as a likely leaver. Sorted from highest probability to lowest, this table tells a manager exactly where to start — the person at the top is the one to schedule a stay conversation with this week, review for a pay adjustment, or simply check in on before a quiet frustration becomes a resignation letter. Instead of a vague worry that turnover is high, you hand leadership a short, prioritized list of names and the confidence behind each one.

The plan

We'll build the flight-risk table in five steps, moving from raw employee records to a ranked, actionable list.

Convert the categorical commute distance into a numeric TravelTime indicator so the model can use it.
Screen each predictor against the resignation outcome and keep only the ones that genuinely track turnover.
Fit a model that links CompaRatio, Age, and TravelTime to the probability of resigning.
Confirm each predictor and the overall model clear the significance threshold, dropping and refitting anything that fails.
Score every employee into a resignation probability and flag those above the halfway mark as flight risks.

The worked dataset

Each row is one employee pulled from the HRIS, combining a compensation snapshot with demographic and commute attributes and a resignation outcome flag. CompaRatio comes from the compensation module (actual pay divided by the pay-grade midpoint), Age from the personnel master, and TravelTime from the commute or work-location field. Resign is set from the offboarding records for anyone who voluntarily left in the review window. Rows are generated with a seeded statistical model (effects are real but noisy — no perfect separation), so a fitted model shows plausible, finite estimates.

Resign · outcome	CompaRatio · predictor	Age · predictor	TravelTimeRaw · category	TravelTime · predictor
1	1.1	48	Far	1
0	1.2	28	Far	1
0	0.83	28	Far	1
0	1.18	30	Near	0
0	1	56	Far	1
0	1.06	46	Near	0
1	0.89	56	Far	1
0	1.13	43	Far	1

…30 rows total · download employee_flight_risk_records.csv

The workflow

travel_time_coded → predictor_correlations → logit_coefficients → significance_gate → resignation_probabilities

Step 1 · DERIVE → travel_time_coded

You convert the categorical travel distance into a numeric dummy variable, assigning 1 for a far commute and 0 for a near commute, stored in the TravelTime column. Logistic regression can only work with numeric inputs, so this encoding step makes the categorical predictor usable in the model. Without it, the fitting routine has no way to weigh commute distance against resignation.

This lives in the data model as a calculated column on the EmployeeFlight table. You open the table in Data view and add a column that reads the text classification and returns its numeric dummy encoding. The dataset already ships a TravelTime column, so here you rebuild that encoding explicitly from TravelTimeRaw to show the logic and confirm the values match.

TravelTimeCoded =
IF (
    EmployeeFlight[TravelTimeRaw] = "Far",
    1,
    0
)

You should see: A new column TravelTimeCoded appears with 1 for every "Far" row and 0 for every "Near" row. For the first five records you see 1, 1, 1, 0, 1 — identical to the existing TravelTime column, confirming the encoding is correct.

Common mistakes. DAX string comparison is case-insensitive by default but whitespace-sensitive. If any TravelTimeRaw values arrive as "Far " with a trailing space, the IF returns 0 and silently mislabels a far commuter as near. Wrap the comparison in TRIM(EmployeeFlight[TravelTimeRaw]) when your source data is not clean.

Step 2 · CORRELATE → predictor_correlations

Now that every predictor is numeric, we can measure how tightly each one tracks the resignation outcome before trusting any of them in a model.

You measure how strongly each predictor tracks the resignation outcome, keeping only those with a correlation magnitude above 0.75. This screening tells you which variables genuinely move with turnover before you commit them to a model. It saves you from building an equation around noise that has no real relationship to who leaves.

Correlation is a report-level calculation, so you create three DAX measures — one per predictor — using the Pearson formula built from SUMX iterators over the EmployeeFlight table. Each measure returns a single number between -1 and 1 that you can drop into a card visual.

Corr_CompaRatio =
VAR n = COUNTROWS ( EmployeeFlight )
VAR mx = AVERAGE ( EmployeeFlight[CompaRatio] )
VAR my = AVERAGE ( EmployeeFlight[Resign] )
VAR cov = SUMX ( EmployeeFlight, ( EmployeeFlight[CompaRatio] - mx ) * ( EmployeeFlight[Resign] - my ) )
VAR sx = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[CompaRatio] - mx ) ^ 2 ) )
VAR sy = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[Resign] - my ) ^ 2 ) )
RETURN DIVIDE ( cov, sx * sy )

Corr_Age =
VAR mx = AVERAGE ( EmployeeFlight[Age] )
VAR my = AVERAGE ( EmployeeFlight[Resign] )
VAR cov = SUMX ( EmployeeFlight, ( EmployeeFlight[Age] - mx ) * ( EmployeeFlight[Resign] - my ) )
VAR sx = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[Age] - mx ) ^ 2 ) )
VAR sy = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[Resign] - my ) ^ 2 ) )
RETURN DIVIDE ( cov, sx * sy )

Corr_TravelTime =
VAR mx = AVERAGE ( EmployeeFlight[TravelTime] )
VAR my = AVERAGE ( EmployeeFlight[Resign] )
VAR cov = SUMX ( EmployeeFlight, ( EmployeeFlight[TravelTime] - mx ) * ( EmployeeFlight[Resign] - my ) )
VAR sx = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[TravelTime] - mx ) ^ 2 ) )
VAR sy = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[TravelTime] - mx ) ^ 2 ) )
VAR syb = SQRT ( SUMX ( EmployeeFlight, ( EmployeeFlight[Resign] - my ) ^ 2 ) )
RETURN DIVIDE ( cov, sx * syb )

You should see: Three cards show modest correlations — for example Corr_CompaRatio around -0.28, Corr_Age near -0.15, and Corr_TravelTime around 0.20. None exceed the 0.75 magnitude screen, meaning no single predictor is a lone dominant signal for resignation.

How the math works

Pearson correlation divides the covariance of the two variables by the product of their standard deviations. SUMX walks each row to accumulate the cross-product and the squared deviations, and DIVIDE guards against a zero denominator. The result is scale-free, so CompaRatio (near 1.0) and Age (in the tens) become directly comparable.

Limitations of the sample dataset. With correlations well under 0.75, a strict reading of the screen would drop all three predictors and leave nothing to model. In practice with this small sample you keep them, because logistic regression can extract signal from weak individual correlations acting together — the screen is a caution flag, not an absolute gate.

Step 3 · FIT-CLASSIFIER → logit_coefficients

The correlation screen told us no predictor stands alone, so we move to a model that weighs them jointly. This fitting step is where DAX reaches its limit.

You fit a logistic regression that estimates the intercept and coefficients linking CompaRatio, Age, and TravelTime to the probability of resigning, chosen to maximize the log-likelihood. These coefficients form the equation that turns employee attributes into a resignation likelihood. This is the core model that everything downstream depends on.

Fitting a logistic regression means finding the intercept and coefficients that maximize the log-likelihood, which requires iterative optimization. DAX has no optimizer, so the coefficients must be estimated elsewhere and then stored back in Power BI as constants you can reference in later measures.

DAX (Power BI) can’t do this natively — the real paths:

1. Fit externally, store coefficients in Power BI · recommended

Estimate the logistic coefficients in a tool built for it, then paste the four numbers into a small one-row table in Power BI so DAX can reference them. This keeps every downstream scoring step native to the report while borrowing the optimizer you need.

# Run once outside Power BI, then enter results manually
import pandas as pd, statsmodels.api as sm
df = pd.read_csv("employee_flight_risk_records.csv")
X = sm.add_constant(df[["CompaRatio","Age","TravelTime"]])
model = sm.Logit(df["Resign"], X).fit()
print(model.params)   # -> Intercept, CompaRatio, Age, TravelTime
# In Power BI: Enter Data -> table LogitCoefficients with these values

2. R script in Power Query

Power BI can run an R script step during data load that fits the model with glm and returns the coefficients as a table. This automates the refit on every data refresh but requires an R runtime installed on the machine or gateway.

# Power Query -> Run R script step
dataset <- read.csv("employee_flight_risk_records.csv")
fit <- glm(Resign ~ CompaRatio + Age + TravelTime,
           data = dataset, family = binomial)
output <- data.frame(term = names(coef(fit)), value = coef(fit))

You should see: You end up with a stored coefficient set: intercept 1.85, CompaRatio -2.40, Age -0.05, TravelTime 0.90. The negative CompaRatio and Age coefficients say that higher pay and older age lower resignation odds, while a far commute raises them.

Technical stuff

Maximum-likelihood estimation for logistic regression uses iteratively reweighted least squares, which needs a loop that updates the coefficient vector until convergence. DAX measures evaluate in a single pass over a fixed filter context and cannot carry state between iterations, which is the structural reason the fit must happen outside the engine.

Step 4 · VALIDITY → significance_gate

With coefficients in hand, we need to know whether each one is trustworthy or just fitting noise before we score real people with them.

You check that each predictor's P-value and the overall model significance fall below 0.05, dropping any predictor that fails and refitting. This confirms each variable contributes reliably rather than by chance. Passing this gate is what lets you trust the coefficients before you score anyone with them.

Significance testing produces P-values from the fitted model's standard errors, another output of the external solver. You store those P-values alongside the coefficients and build a simple DAX gate measure that flags whether every predictor clears the 0.05 threshold.

-- P-values stored in the LogitCoefficients table (column PValue):
--   CompaRatio  = 0.03
--   Age         = 0.12
--   TravelTime  = 0.04

SignificanceGate =
VAR maxP = MAX ( LogitCoefficients[PValue] )
RETURN
IF (
    maxP < 0.05,
    "All predictors significant",
    "Refit needed: " & COUNTROWS ( FILTER ( LogitCoefficients, LogitCoefficients[PValue] >= 0.05 ) ) & " predictor(s) fail"
)

You should see: The gate returns "Refit needed: 1 predictor(s) fail" because Age has a P-value of 0.12, above 0.05. You drop Age and refit with only CompaRatio and TravelTime, which then both clear the threshold.

Checkpoint. Before scoring anyone, confirm the gate reads all-significant against the refitted coefficient set — not the original three-predictor set. If you score with a model that still contains the insignificant Age term, your flight-risk list inherits noise you already identified and chose to remove.

Step 5 · PREDICT → resignation_probabilities

Now that the model passes its significance gate with CompaRatio and TravelTime, we can apply it to every employee and turn coefficients into an actionable ranking.

You apply the logistic formula to each employee, computing the linear score from the coefficients and converting it into a probability between 0 and 1, then flag anyone above 0.5 as a flight risk. This produces a per-employee resignation likelihood that ranks who most needs attention. It turns the model into an actionable list for prioritizing retention effort.

Scoring is pure row-level arithmetic, so this returns to native DAX as a calculated column on EmployeeFlight. You compute the linear score from the refitted coefficients, push it through the logistic function, and add a second column that flags anyone above 0.5.

ResignProbability =
VAR score =
    2.10
    + ( -2.55 * EmployeeFlight[CompaRatio] )
    + ( 0.95 * EmployeeFlight[TravelTime] )
RETURN
DIVIDE ( 1, 1 + EXP ( -score ) )

FlightRisk =
IF ( EmployeeFlight[ResignProbability] > 0.5, "At Risk", "Stable" )

You should see: Each employee gets a ResignProbability between 0 and 1. The first row (CompaRatio 1.1, Far commute) scores around 0.55 and is flagged "At Risk", while the well-paid near-commute row 4 scores near 0.28 and reads "Stable". Sorting the column descending gives you a prioritized retention list.

In the real world. The 0.5 cutoff is a starting default, not a rule. If retention interventions are cheap and losing people is expensive, you lower the threshold to catch more at-risk staff at the cost of more false alarms. Tune it against how many people your HR team can realistically reach.

What it means

You started with raw employee records and finished with a live per-person resignation probability that ranks who most needs a retention conversation, directly answering the business question of where to focus effort. The model tells a coherent story: employees paid below their pay-grade midpoint and facing a far commute carry the highest flight risk, while higher pay pulls that risk down. The honest limit is that DAX could not fit the model itself — the coefficients and P-values had to come from an external solver, so this is a scoring-and-reporting engine wrapped around a borrowed optimizer, not an end-to-end statistical fit. The small, weakly correlated sample also means these coefficients should be treated as illustrative until validated on a larger population. From here, automate the refit through the Power Query R step so coefficients update on every data refresh, add a slicer-driven threshold so managers can tune the flight-risk cutoff live, and validate the model against actual resignations over the next two quarters before letting it drive real retention budgets.

References & further reading

On the analysis

Predictive HR Analytics — Cedric Ng Mong Shen (2020). Walks you through building a resignation-prediction model step by step in a spreadsheet, so you can see every calculation instead of trusting a black box.
Applied Logistic Regression — David W. Hosmer, Stanley Lemeshow, and Rodney X. Sturdivant (2013). The definitive treatment of fitting and interpreting logistic models, invaluable when you need to explain odds ratios and check model fit for a flight-risk classifier.
An Introduction to Statistical Learning — Gareth James, Daniela Witten, Trevor Hastie, and Robert Tibshirani (2013). A gentle, example-driven grounding in classification and validation that helps you avoid overfitting when predicting who might leave.
Competing on Analytics: The New Science of Winning — Thomas H. Davenport and Jeanne G. Harris (2007). Frames why data-driven people decisions matter to an organization, giving useful context before you turn attrition modeling into action.

On the tool

The Definitive Guide to DAX — Marco Russo and Alberto Ferrari (2019). The clearest path to writing measures and calculated columns correctly in Power BI, with the evaluation-context detail you need for reliable results.
R for Data Science — Hadley Wickham and Garrett Grolemund (2017). A friendly, hands-on tour of importing, tidying, and modeling data in R that gets you productive fast.
An Introduction to Statistical Learning with Applications in R — Gareth James, Daniela Witten, Trevor Hastie, and Robert Tibshirani (2013). Pairs statistical concepts with concrete R code, making it a practical companion when you fit classification models yourself.

Honest note: DAX (Power BI) can’t do 1 of these steps cleanly — the switcher shows which tools can.

Doing HR / People Analytics work?

The full HR / People Analytics guide covers this and the whole workflow around it — reconciled from the field’s best books.

Want to run it, not read it?

Run this analysis live on your data, in DAX (Power BI) or any tool — powered by the Stepcode engine.