Skip to content
Tutorials Center
My learning

Topics

Lesson 53 of 56 ยท Projects

Project: Risk and Fraud Monitoring Dashboard

Build a fraud and risk monitoring dashboard in Power BI: rule-based flags, a risk score, adjustable thresholds, precision and recall, drill-through to transactions and alerts.

A risk team looks at thousands of transactions a day and has time to examine a few dozen. A monitoring dashboard has a clear job: put the few transactions that deserve a human's attention in front of the analyst, and show how well the rules are working. That means flags and thresholds, a way to drill from a number into the individual transactions, a measure of how many flagged cases are real, and an alert when something spikes.

This project builds such a dashboard on synthetic card data. You will design rule-based flags (large amount, unusual for the account, foreign country, burst of transactions), combine them into a risk score, expose a threshold as a parameter, measure precision and recall, drill through to the evidence and add alerts. Power BI is a GUI tool and cannot run on this site, so the DAX is read-only. The runnable examples are pandas stand-ins that compute the same values on 3,000 generated transactions, 32 of which are confirmed fraud.

The data is synthetic. Real fraud monitoring involves personal and financial data, legal obligations and usually a dedicated machine-learning system. A Power BI dashboard sits on top of those systems: it is the place where analysts review and manage the work, not the engine that detects the fraud.

What the dashboard answers

Four jobs for the report

What is flagged? Transactions that break a rule.
How strict are we? Thresholds you can change.
Are the flags right? Precision and recall on confirmed cases.
Is something spiking? Alerts when the flag rate jumps.
1 / 2

The model

  • Transactions (fact table): Transaction ID, Account ID, Timestamp, Amount, Country, Is Confirmed Fraud (a label that is only known after investigation).
  • Accounts (dimension): Account ID, Home Country, segment.
  • Date, as in Date Tables and Time Intelligence Setup.
  • A disconnected Amount Threshold table created with the What-if parameter feature (Modeling > New parameter), which generates GENERATESERIES and a slicer.

Relate Accounts to Transactions one-to-many and 'Date'[Date] to a Transactions[Date] column (the date part of the timestamp, so the relationship is on a day, not a minute).

Rules as columns, thresholds as measures

Rules that depend only on the row and on fixed history are good calculated columns: they are computed once at refresh and can be used in slicers and tables.

DAX
Foreign Country = Transactions[Country] <> RELATED ( Accounts[Home Country] )

Txns Last Hour =
VAR ThisAccount = Transactions[Account ID]
VAR ThisTime    = Transactions[Timestamp]
RETURN
    COUNTROWS (
        FILTER (
            Transactions,
            Transactions[Account ID] = ThisAccount
                && Transactions[Timestamp] <= ThisTime
                && Transactions[Timestamp] > ThisTime - TIME ( 1, 0, 0 )
        )
    )

Burst Flag = Transactions[Txns Last Hour] >= 4

Amount Z Score =
VAR ThisAccount = Transactions[Account ID]
VAR Avg = CALCULATE ( AVERAGE ( Transactions[Amount] ), ALLEXCEPT ( Transactions, Transactions[Account ID] ) )
VAR Sd  = CALCULATE ( STDEV.S ( Transactions[Amount] ), ALLEXCEPT ( Transactions, Transactions[Account ID] ) )
RETURN
    DIVIDE ( Transactions[Amount] - Avg, Sd )

Unusual Flag = Transactions[Amount Z Score] > 3

A few important notes:

  • Txns Last Hour compares every row with every other row of the same account, so it gets slow on millions of rows. In production, compute rolling counts upstream, in SQL or in Power Query, and import the result. A DAX column is fine for a demonstration and for modest volumes.
  • The z-score measures how many standard deviations a transaction is above the account's own average. It makes an amount that is normal for a high-spending account less suspicious than the same amount on a thrifty one. It uses STDEV.S and DIVIDE, explained in DAX Math and Statistical Functions.
  • A calculated column cannot react to a slicer. A threshold the analyst changes must live in a measure:
DAX
Amount Threshold Value = SELECTEDVALUE ( 'Amount Threshold'[Amount Threshold], 800 )

Large Amount Transactions =
CALCULATE (
    COUNTROWS ( Transactions ),
    FILTER ( Transactions, Transactions[Amount] > [Amount Threshold Value] )
)

SELECTEDVALUE returns the slicer's value, or a default of 800 when nothing is selected (see DAX Logical and Information Functions). Moving the slicer changes the number of flagged transactions instantly.

A risk score

Count how many rules a transaction breaks, and name the level:

DAX
Risk Score =
    IF ( Transactions[Amount] > 800, 1, 0 )
        + IF ( Transactions[Unusual Flag], 1, 0 )
        + IF ( Transactions[Foreign Country], 1, 0 )
        + IF ( Transactions[Burst Flag], 1, 0 )

Risk Level =
SWITCH (
    TRUE ( ),
    Transactions[Risk Score] >= 3, "High",
    Transactions[Risk Score] = 2,  "Medium",
    Transactions[Risk Score] = 1,  "Low",
    "None"
)

A weighted score, where a burst counts more than a foreign country, is a natural next step; start with equal weights so everyone can follow the logic.

Try it: generate the data and score it

Python Stand-in: four rules and a risk score on 3,000 transactions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The score is doing its job: of 2,856 transactions with score 0, none are fraud, while 5 of the 6 transactions with score 3 are. The analyst can start at the top and work down. The Foreign rule alone fires on 117 transactions, mostly harmless travel, which is why a single rule is a poor alarm.

Precision, recall and the cost of thresholds

A flag is useful only if it is right often enough. For the transactions whose label is known:

  • Precision is the share of flagged transactions that are real fraud. Low precision means analysts waste time and learn to ignore the alerts (alert fatigue).
  • Recall is the share of real fraud that was flagged. Low recall means fraud slips through.

They pull in opposite directions: a lower threshold catches more fraud and flags more innocent transactions.

DAX
Flagged Transactions =
CALCULATE ( COUNTROWS ( Transactions ), Transactions[Risk Score] >= [Score Cutoff] )

Flagged Fraud =
CALCULATE (
    COUNTROWS ( Transactions ),
    Transactions[Risk Score] >= [Score Cutoff],
    Transactions[Is Confirmed Fraud] = TRUE ( )
)

Confirmed Fraud =
CALCULATE ( COUNTROWS ( Transactions ), Transactions[Is Confirmed Fraud] = TRUE ( ) )

Precision = DIVIDE ( [Flagged Fraud], [Flagged Transactions] )
Recall    = DIVIDE ( [Flagged Fraud], [Confirmed Fraud] )
Flag Rate = DIVIDE ( [Flagged Transactions], COUNTROWS ( Transactions ) )

[Score Cutoff] is a second what-if parameter. Putting precision and recall on the report, next to the slicer, lets the risk manager choose a point on the trade-off with open eyes. The stand-in sweeps both kinds of threshold on the same data (the generator is the same as above).

Python Stand-in: precision and recall for an amount threshold and for a score cutoff

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The amount rule alone cannot do well: at 200 it flags 237 transactions to catch 69 percent of the fraud with 9 percent precision (most flags are innocent), and at 1,600 it flags 3 with perfect precision but finds only 9 percent. The score cutoff of 2 is a much better compromise, with 23 flags, 78 percent precision and 56 percent recall, because it asks for two independent signals. The dashboard should make that trade-off visible, and the analyst should decide on purpose.

The report pages

  1. Overview. Cards for Flagged Transactions, Flag Rate, Precision and Recall; a daily line chart of flagged transactions with the Analytics pane's anomaly detection turned on (The Analytics Pane); the two threshold slicers.
  2. Triage queue. A table of flagged transactions sorted by Risk Score descending then amount, with conditional formatting on the level (Conditional Formatting, KPIs and Cards). A bar chart of flags by rule shows which rule produces the most work.
  3. Accounts. The accounts with the most flagged transactions, and a matrix of country by rule.
  4. Detail (drill-through). Right-click an account in the triage table and choose Drill through > Account detail: a page filtered to that account, with all its transactions in order, a timeline, a map or list of countries and the reasons it was flagged. See Drill-through, Tooltips, Bookmarks and Buttons for setting up a drill-through page, and add a tooltip page showing the last five transactions when the analyst hovers over an account.

Alerts

Reports should not need a person to open them. In the service, pin the Flag Rate card (or a KPI or gauge) to a dashboard and set a data alert: notify when the value rises above a limit. An alert rule fires after each data refresh, so the freshness of the data decides how quickly you are told (Power BI Service and Scheduled Refresh and Gateways). For more complex actions, such as creating a ticket or messaging a channel, alerts can start a Power Automate flow; check which options your tenant offers.

Alerts need tuning, as thresholds do: a rule that fires every day is ignored by the second week. The stand-in below shows a self-adjusting rule: alert when today's flag count is more than three times the average of the previous seven days.

Python Stand-in: an alert that adapts to the recent average

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Only the spike of 19 on 10 May fires. A fixed limit of 10 would have done the same here, but a fixed limit would miss a small account's change or fire daily as volume grows. In Power BI the simplest equivalent is a measure that returns the ratio of today's flags to a trailing average (using the date functions from Time Intelligence), and an alert on that measure.

Governance for a sensitive report

  • Row-level security so each analyst sees only the accounts of their region or portfolio (Row-Level Security).
  • Sensitivity labels on the semantic model and report, so exports keep their classification (Governance and Deployment).
  • Keep personal data out: use pseudonymized account IDs in the model, and look up names in the source system, not in the dashboard.
  • Do not use the dashboard as the system of record. Case status, decisions and audit trails belong in the case management system.

Final checks

  • The flag counts and precision and recall match the pandas figures.
  • Changing a threshold slicer changes only measures, never calculated columns.
  • The drill-through page works from the triage table and shows the same transactions for an account as the source.
  • The alert is tested by temporarily lowering its threshold.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why must an adjustable amount threshold be a measure and not a calculated column?

    Columns are evaluated when the data is refreshed. Anything that depends on a slicer's current value has to be a measure.
  2. 2. What does precision tell you?

    Precision is flagged and real divided by all flagged. Recall is flagged and real divided by all real fraud.
  3. 3. Why is a combined risk score better than a single amount rule in the example?

    A score cutoff of 2 flagged 23 transactions with 78 percent precision and 56 percent recall, a better balance than any single amount threshold.
  4. 4. Which Power BI feature takes the analyst from a flagged account to all of its transactions?

    A drill-through page filtered by Account ID shows the evidence behind a flagged number.
  5. 5. A data alert on a dashboard tile is evaluated when?

    Alerts check the tile's value after each refresh, so the refresh schedule limits how quickly you are told.

Summary

  • Rule-based flags (large amount, unusual for the account, foreign country, burst) become calculated columns; analyst-adjustable thresholds must be measures driven by what-if parameters.
  • A risk score that counts independent signals beats any single rule: measure it with precision and recall on confirmed cases and make the trade-off visible.
  • Use drill-through for evidence, the Analytics pane for anomaly detection and data alerts with adaptive rules to avoid alert fatigue.
  • Protect the report with RLS, labels and pseudonymized data, and keep case decisions in the system of record.

Next: test your skills with Exercises with Solutions.