When a Green KPI Hides Missing Customer Rows in Power BI

Start With the Row Count, Not the KPI

The most dangerous weekly report is not the one that turns red. It is the one that stays green while underlying data quietly disappears. In finance systems work, a Power BI KPI card can show a green checkmark because the measured value is above threshold, while the SQL view feeding that card has already dropped rows that should have been included.

The failure is silent because both layers are working as designed. SQL Server applies the join logic exactly as written, and Power BI applies the KPI logic exactly as configured. Neither layer asks whether the population is complete. That question has to come from a control outside the visual.

The Null Join Drops Rows Before the Measure Exists

In SQL Server, NULL values in joined columns do not match each other. An INNER JOIN returns only matching pairs and discards unmatched rows from both tables. If a customer record has a NULL industry code, and the industry code is used in an inner join, that record is discarded before any Power BI measure is calculated.

This is easy to miss in a view with several joins. The query still runs. The report still refreshes. The dashboards still publish. The only symptom may be a difference between the row count in the source table and the row count in the view. That difference is a reconciliation signal, not an error message.

Common NULL-handling functions such as COALESCE and ISNULL can replace the NULL with a meaningful value. For example, COALESCE(c.industry_code, 'Unclassified') keeps the customer row in the model while flagging the missing industry. Without that treatment, the row simply ceases to exist in the report layer.

Power BI Turns Missing Data Into a Blank, Then Hides the Blank

A Power BI KPI visual requires a base measure that evaluates to a value, a target measure or value, and a threshold or goal. It does not check coverage. A green checkmark indicates that the base measure increased relative to target or threshold; it does not indicate that all expected cases were included in the base measure.

Power BI visuals, by default, omit rows and columns that have blank measure values. When a customer is dropped upstream, the measure for that grouping is blank. The visual then removes the blank grouping. The remaining rows may still produce a favourable KPI because the excluded customers do not enter the calculation.

This is why a green card can coexist with a missing customer list. The KPI visual is a presentation of the measure, not an audit of the source. The audit has to be built separately, using row counts and explicit blank checks before the KPI is trusted.

A Validation Order That Stops the Silent Drop

The control sequence starts in SQL, not in Power BI. Reconcile the view row count against the source table. If the view returns fewer rows than the source, identify every join that could drop NULL keys. Use COUNT(column_name) to check how many values are NULL in the key columns.

Replace silent inner joins with an explicit outer join where missing matches should be retained, or use COALESCE or ISNULL when a default category is acceptable. The chosen treatment depends on the reporting question. If missing industry is meaningful, keep it visible as Unclassified. If the row is truly outside the analysis, document that exclusion and reconcile it.

In Power BI, enable Show items with no data on a supporting table so blank groups remain visible during review. Add a DAX measure using ISBLANK to count how many customers or transactions have blank industry. Pin that exception count next to the KPI. A green KPI is only acceptable when the exception count is zero or has been reviewed and accepted.

A Worked Example: The Distributor With 214 Active Customers

A distributor has 214 active customers in the source customer table. The weekly revenue KPI uses a SQL view that joins Customers to Sales with an inner join on customer_key and industry_code. Twelve customers have a NULL industry_code in the source. Because NULLs do not match in the join, those twelve rows are discarded.

The Power BI KPI card shows revenue above target and a green checkmark. The board pack is produced on time. No error appears in the refresh. A regional manager later asks why a known account is absent from the underlying customer listing. That question exposes the twelve missing rows.

Fix the view by changing the join path to keep customers with missing industry, using COALESCE(c.industry_code, 'Unclassified'). Add a reconciliation table in Power BI with Show items with no data enabled, showing all 214 customers and an exception count for Unclassified. The KPI still works, but the pack now carries a visible completeness check instead of a silent gap.

Limitation: Where the Control Layer Breaks

The technical fix has a clear limitation. COALESCE or an outer join can restore the missing rows, but neither tells the finance team whether those rows are material. A large consolidated report may tolerate ten missing rows in a non-material segment. Forcing all missing industries into the model can create noise and distract from the main message.

The opposite risk is also present. An analyst can classify every NULL as Unclassified and stop investigating why the source system does not capture industry. The pattern surfaces the missing rows; it does not decide which missing rows require a restatement, a source system correction, or a threshold-based note in the pack. That judgment belongs to the finance owner.

Monday Morning Takeaway

The takeaway is reconciliation before presentation. A KPI icon is a display result, not a completeness control. The control must sit earlier in the flow: row counts, null checks, and blank-value exceptions before a green metric is accepted.