Power BI for Drug Diversion Prevention

Manual review of ADC transaction logs and wastage records is labor-intensive and surface-level. The case for a dedicated analytics layer is well-supported:

The SQL patterns in Analytics Dashboard Blueprint and Tough Issues produce the staging tables this Power BI model consumes. This page does not duplicate that SQL content — it starts where those pages end: data already landed in a staging schema, ready to be modeled.

Diversion surveillance requires integrating six or more source systems, each with its own data model and extract format. The table below lists the minimum required extracts and the key fields to capture from each.

Cross-System Challenges

PHI Minimization at Source

Never load patient names, MRNs, or dates of birth into the Power BI dataset. Instead, apply an HMAC-keyed hash in the Silver layer to produce a PatientToken:

-- SQL Silver layer: hash the MRN, drop the original
SELECT
    CONVERT(VARCHAR(64),
        HASHBYTES('SHA2_256', CONVERT(VARCHAR, patient_mrn) + @salt),
        2) AS PatientToken,
    dispense_datetime,
    user_id,
    medication_id,
    qty_dispensed
    -- patient_mrn intentionally excluded from SELECT
FROM bronze.adc_dispense;

The PatientToken allows dispense-to-MAR reconciliation (join two fact tables on the same token) without exposing PHI. Keep the HMAC key in a secrets store — not in the Power BI dataset or M code.

Fact Tables

Dimension Tables

Relationship Rules

• All relationships are 1:many (dimension → fact).
• DimDate has one active relationship per fact table (DispenseDate → FactDispense). Additional date columns (AdminDate, WasteDate) are inactive; activate them per-measure with USERELATIONSHIP.
• Use single cross-filter direction on all relationships. Bidirectional cross-filter causes unexpected filter leakage between fact tables.
• Hide all surrogate key columns from Report View. Users should navigate by names and labels, not integer keys.
• Disable Auto Date/Time globally (File → Options → Data Load → uncheck Auto date/time). It creates hidden date tables for every date column and inflates model size significantly.

Compute row-level fields (lags, flags, durations) in Power Query — not as DAX calculated columns. Calculated columns in DAX are evaluated at refresh time and stored in the model, but they cannot be compressed as effectively as imported columns and they cannot use parallelism available in the engine. Do the heavy lifting in M, land clean columns in the fact tables.

1 — Incremental Refresh Filter (FactDispense)

Declare RangeStart and RangeEnd as Power BI parameters (type Date/Time, marked as query parameters in the Manage Parameters dialog). Power BI rewrites these as server-side predicates during incremental refresh so only the new partition is loaded from the database.

let
    Source = Sql.Database("sql-server", "GoldDB"),
    FactDispense_Raw = Source{[Schema="gold", Item="FactDispense"]}[Data],
    // Incremental refresh: Power BI folds these predicates to SQL WHERE
    Filtered = Table.SelectRows(
        FactDispense_Raw,
        each [DispenseDateTime] >= RangeStart
             and [DispenseDateTime] < RangeEnd
    ),
    // PHI minimization: drop any columns that should not leave SQL Server
    RemovedPHI = Table.RemoveColumns(
        Filtered,
        {"PatientFirstName", "PatientLastName", "PatientDOB"}
    )
in
    RemovedPHI

2 — Date Dimension Generator

let
    StartDate   = #date(2020, 1, 1),
    EndDate     = #date(2030, 12, 31),
    DateList    = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    DateTable   = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    TypedDates  = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),
    AddYear     = Table.AddColumn(TypedDates,  "Year",        each Date.Year([Date]),    Int64.Type),
    AddMonth    = Table.AddColumn(AddYear,     "MonthNo",     each Date.Month([Date]),   Int64.Type),
    AddMonthName= Table.AddColumn(AddMonth,    "MonthName",   each Date.ToText([Date], "MMMM"), type text),
    AddQuarter  = Table.AddColumn(AddMonthName,"Quarter",     each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddWeekday  = Table.AddColumn(AddQuarter,  "DayOfWeek",   each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
    AddIsWeekend= Table.AddColumn(AddWeekday,  "IsWeekend",   each Date.DayOfWeek([Date], Day.Monday) >= 5, type logical),
    AddDateKey  = Table.AddColumn(AddIsWeekend,"DateKey",     each Date.Year([Date])*10000 + Date.Month([Date])*100 + Date.Day([Date]), Int64.Type)
in
    AddDateKey

3 — User Identity Merge (ADC ↔ EMR ↔ HR)

Employee ID is the anchor key. ADC and EMR use different login IDs; HR is authoritative. Use LeftOuter joins so ADC users not yet in HR (termination lag) still appear in the model.

let
    ADC_Users  = /* query returning ADC_UserID, EmployeeID */,
    EMR_Users  = /* query returning EMR_UserID, EmployeeID */,
    HR_Users   = /* query returning EmployeeID, Name, Role, Department, HireDate, TermDate */,

    // Join ADC → HR on EmployeeID (LeftOuter keeps ADC users missing from HR)
    ADC_HR = Table.NestedJoin(
        ADC_Users, {"EmployeeID"},
        HR_Users,  {"EmployeeID"},
        "HR", JoinKind.LeftOuter
    ),
    ExpandADC_HR = Table.ExpandTableColumn(ADC_HR, "HR",
        {"Name", "Role", "Department", "HireDate", "TermDate"}),

    // Join result → EMR on EmployeeID to add EMR_UserID
    WithEMR = Table.NestedJoin(
        ExpandADC_HR, {"EmployeeID"},
        EMR_Users,    {"EmployeeID"},
        "EMR", JoinKind.LeftOuter
    ),
    ExpandEMR = Table.ExpandTableColumn(WithEMR, "EMR", {"EMR_UserID"})
in
    ExpandEMR

4 — Unpivot Multi-Witness Waste Columns

Some ADC exports produce wide-format waste records with Witness1, Witness2, Witness3, Witness4 columns. Unpivot before loading so each witness becomes a row — this enables witness-compliance analysis in DAX.

let
    Source         = /* waste table with Witness1..Witness4 columns */,
    // Identify columns to keep as-is
    AnchorColumns  = {"WasteID", "WasteDateTime", "WasterUserID",
                      "WasteQuantity", "WasteReason"},
    // Unpivot: every non-anchor column becomes an AttributeName / AttributeValue row
    Unpivoted      = Table.UnpivotOtherColumns(Source, AnchorColumns,
                         "WitnessPosition", "WitnessUserID"),
    // Remove rows where no witness was entered (nulls from empty columns)
    Cleaned        = Table.SelectRows(Unpivoted,
                         each [WitnessUserID] <> null and [WitnessUserID] <> "")
in
    Cleaned

5 — Off-Hours Classifier (Custom Function)

// Paste as a new query named "fnIsOffHours", then invoke as a custom column
(dispenseDateTime as datetime) as logical =>
let
    t         = Time.From(dispenseDateTime),
    dow       = Date.DayOfWeek(DateTime.Date(dispenseDateTime), Day.Monday),
    IsWeekend = dow >= 5,  // Saturday = 5, Sunday = 6
    IsNight   = t >= #time(19,0,0) or t < #time(7,0,0)
in
    IsWeekend or IsNight

6 — Minutes-to-Waste Column

let
    Source = /* FactWaste_Silver with DispenseDateTime and WasteDateTime */,
    AddMinutesToWaste = Table.AddColumn(
        Source,
        "MinutesToWaste",
        each Duration.TotalMinutes([WasteDateTime] - [DispenseDateTime]),
        type number
    )
in
    AddMinutesToWaste

7 — LEFT OUTER Join: Un-Charted Dispenses Survive

ADC dispenses that have no matching MAR record are a key diversion signal. The ChartedFlag column flags them; do not drop the NULLs.

let
    ADC_Dispenses  = /* Silver ADC dispense table */,
    MAR_Records    = /* Silver MAR table with DispenseID linkage */,

    // LeftOuter so every ADC dispense row survives even without a MAR match
    Joined = Table.NestedJoin(
        ADC_Dispenses, {"DispenseID"},
        MAR_Records,   {"DispenseID"},
        "MAR", JoinKind.LeftOuter
    ),
    Expand = Table.ExpandTableColumn(Joined, "MAR",
                 {"AdminDateTime", "DocumentedBy"}, {"AdminDateTime", "DocumentedBy"}),

    // False = dispensed but never charted = diversion signal
    AddChartedFlag = Table.AddColumn(Expand, "ChartedFlag",
        each [AdminDateTime] <> null, type logical)
in
    AddChartedFlag

Waste Rate & Peer Benchmarking

-- Detects: individual waste rate vs. volume administered
User Waste Rate =
    DIVIDE(
        SUM(FactWaste[WasteQuantity]),
        SUM(FactWaste[WasteQuantity]) + SUM(FactDispense[QuantityAdministered]),
        0
    )

-- Detects: role/department peer average for z-score baseline
Peer Waste Rate Avg =
    CALCULATE(
        AVERAGEX(VALUES(DimUser[UserKey]), [User Waste Rate]),
        ALLEXCEPT(DimUser, DimUser[Role], DimUser[Department])
    )

-- Detects: spread of waste rates within peer group (denominator for z-score)
Peer Waste Rate StdDev =
    CALCULATE(
        STDEVX.P(VALUES(DimUser[UserKey]), [User Waste Rate]),
        ALLEXCEPT(DimUser, DimUser[Role], DimUser[Department])
    )

-- Detects: how many standard deviations above/below peer mean; flag at ±2 SD
User Waste Rate Z =
    DIVIDE(
        [User Waste Rate] - [Peer Waste Rate Avg],
        [Peer Waste Rate StdDev],
        0
    )

-- Detects: categorical alert label for conditional formatting and the Anomaly Watchlist
Waste Anomaly Flag =
    SWITCH(
        TRUE(),
        [User Waste Rate Z] >= 2,  "High waste - investigate",
        [User Waste Rate Z] <= -2, "Low waste - verify documentation",
        "Within expected range"
    )

-- Detects: for small peer groups where StdDev is unstable, use 75th percentile threshold
Peer P75 Waste Rate =
    CALCULATE(
        PERCENTILEX.INC(VALUES(DimUser[UserKey]), [User Waste Rate], 0.75),
        ALLEXCEPT(DimUser, DimUser[Role], DimUser[Department])
    )

Late Waste & Temporal Anomalies

-- Detects: Derington definition — waste ≥60 min after administration; Knight definition ≥240 min after dispense
Late Waste Count =
    CALCULATE(
        COUNTROWS(FactWaste),
        FactWaste[MinutesToWaste] >= 60
    )

-- Detects: dispense recorded after administration (clock skew or retroactive entry)
Dispense After Admin Count =
    CALCULATE(
        COUNTROWS(FactDispense),
        FactDispense[DispenseDateTime] > FactDispense[AdministeredDateTime]
    )

-- Detects: 30-day rolling window for trend lines
Dispenses Last 30 Days =
    CALCULATE(
        COUNTROWS(FactDispense),
        DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -30, DAY)
    )

Override Analysis

-- Detects: ADC override pulls with no corresponding active order
Unverified Override Count =
    CALCULATE(
        COUNTROWS(FactDispense),
        FactDispense[IsOverride] = TRUE(),
        FactDispense[OrderExistsFlag] = FALSE()
    )

-- Detects: share of all dispenses that are overrides
Override Rate % =
    DIVIDE(
        CALCULATE(COUNTROWS(FactDispense), FactDispense[IsOverride] = TRUE()),
        COUNTROWS(FactDispense),
        0
    )

-- Detects: normalized override rate for benchmarking across units with different volumes
Overrides per 100 Dispenses =
    DIVIDE(
        CALCULATE(COUNTROWS(FactDispense), FactDispense[IsOverride] = TRUE()) * 100,
        COUNTROWS(FactDispense),
        0
    )

Count Discrepancies

-- Detects: proportion of counts that show a variance
Discrepancy Rate % =
    DIVIDE(
        CALCULATE(COUNTROWS(FactCountDiscrepancy),
                  FactCountDiscrepancy[VarianceQuantity] <> 0),
        COUNTROWS(FactCountDiscrepancy),
        0
    )

-- Detects: total absolute variance units — a better proxy than count of events
Absolute Variance (units) =
    SUMX(FactCountDiscrepancy, ABS(FactCountDiscrepancy[VarianceQuantity]))

-- Detects: which users are responsible for the most unresolved variance
Discrepancy User Rank =
    RANKX(
        ALL(DimUser[UserKey]),
        [Absolute Variance (units)],
        ,
        DESC,
        Dense
    )

Charting Compliance & User Behavior

-- Detects: dispenses with no corresponding MAR entry — primary reconciliation signal
Not Charted Rate % =
    DIVIDE(
        CALCULATE(COUNTROWS(FactDispense), FactDispense[ChartedFlag] = FALSE()),
        COUNTROWS(FactDispense),
        0
    )

-- Detects: off-hours ADC access — higher risk for diversion
Off-Hours Access Count =
    CALCULATE(
        COUNTROWS(FactUserAccess),
        FactUserAccess[IsOffHours] = TRUE()
    )

-- Detects: concentration of dispense volume in a single user — peer-relative outlier
User Dispense Share % =
    DIVIDE(
        COUNTROWS(FactDispense),
        CALCULATE(COUNTROWS(FactDispense), REMOVEFILTERS(DimUser)),
        0
    )

USERELATIONSHIP — Role-Playing Date Example

-- Activate the inactive AdminDate relationship to filter FactDispense by administration date
-- (The active relationship is DispenseDate; this overrides it for this measure only)
Dispenses by Admin Date =
    CALCULATE(
        COUNTROWS(FactDispense),
        USERELATIONSHIP(DimDate[Date], FactDispense[AdministeredDateTime])
    )

Apply slicers consistently across all pages: date range, facility, unit, role, DEA schedule, medication, shift/off-hours, and flag status. Use bookmarks to save role-specific perspectives (Diversion Officer view, Unit Manager view, Executive view) — users activate bookmarks without needing to manually re-apply filters.

Incremental Refresh Configuration

In Power BI Desktop: Transform data → Manage Parameters — create RangeStart and RangeEnd (type Date/Time). In Incremental refresh policy for FactDispense, set: store the last 3 years, refresh the last 7 days. This folds to server-side predicates at refresh time — only the new partition is fetched from the on-prem source via the on-premises data gateway.

Alerting & Monitoring

• Power BI data alert: on the Command Center KPI card for Unverified Override Count, set threshold > 0 → email diversion officer. This operationalizes the ASHP daily override review requirement.
• Power Automate on refresh failure: trigger on "Dataset refresh failed" → Teams/email notification to the analytics team. A failed refresh means the daily override review cannot happen — treat it as a control failure.
• Gateway health: monitor on-premises data gateway heartbeat in Power BI Service Admin portal. Gateway downtime breaks all scheduled refreshes.

HIPAA & Power BI Service

Microsoft's HIPAA Business Associate Agreement (BAA) is available through the Online Services Data Protection Addendum (found in the Service Trust Portal). Power BI Service is covered under that BAA. Signing the BAA is not the same as being compliant — configuration is your responsibility:

• PHI minimization: use PatientToken (HMAC hash), never patient names, MRNs, or dates of birth in the Power BI dataset (45 CFR 164.514 — de-identification).
• Minimum necessary (45 CFR 164.502(b)): RLS ensures users see only the data required for their role. Unit managers see their unit; diversion officers see the full picture.
• Microsoft Purview sensitivity labels: apply "Confidential – PHI Adjacent" or your organization's equivalent label on workspaces and reports. Labels flow to exports.
• MFA and conditional access: enforce via Entra ID (Azure AD) conditional access policies. Power BI Service alone does not enforce MFA.
• Disable exports on sensitive pages: for Page 6 (Investigation Case File), disable export to PDF and CSV in report settings. The data should not leave the governed environment.
• Audit logs: Power BI Service audit logs (Microsoft 365 Compliance Center) capture who viewed what and when. Retain for 90 days minimum; export monthly to your SIEM for HIPAA audit trail requirements.

Dynamic Row-Level Security (RLS)

Define roles in Power BI Desktop under Modeling → Manage Roles. Use USERPRINCIPALNAME() to make the filter dynamic — no need to manually reassign users as staff changes.

-- Role: Diversion Officer — sees all users in all units
-- DAX filter on DimUser table:
[IsDiversionOfficer] =
    LOOKUPVALUE(
        AccessControl[IsDiversionOfficer],
        AccessControl[UPN],
        USERPRINCIPALNAME()
    ) = TRUE()

-- Role: Unit Manager — sees only their assigned unit
-- DAX filter on DimLocation table:
[Unit] =
    LOOKUPVALUE(
        AccessControl[Unit],
        AccessControl[UPN],
        USERPRINCIPALNAME()
    )

-- AccessControl is a small lookup table: UPN, Unit, IsDiversionOfficer
-- Maintained in SharePoint / SQL and loaded as a dimension in the model

DEA Recordkeeping (21 CFR Part 1304)