Complete Data Analytics Tutorial

Power BI, Tableau and Excel share a design decision that explains most of the errors in this track: they let you write an expression without stating what it should be evaluated over. A SQL query names its rows. A DAX measure does not — it inherits its rows from wherever it happens to be dropped, and the same formula will return a correct number in a card, a wrong number in a matrix, and an error in a calculated column.

So the messages sound almost philosophical. A single value for column 'X' cannot be determined. Cannot mix aggregate and non-aggregate arguments. Cannot determine relationships between the fields. None of those are syntax complaints. Each one is the engine saying it was handed an expression and no unambiguous set of rows to run it on. Once you read them that way they stop being mysterious and become the most useful errors in the tool.

Context is the whole subject

Power BI evaluates every expression in two contexts at once. Row context is a single row being iterated — what a calculated column always has, and what functions like SUMX create. Filter context is the set of rows surviving the slicers, the visual's own grouping, and any CALCULATE modifiers — what a measure always has.

Almost every confusing DAX result is one of these two being different from what the author assumed. A calculated column cannot see the visual's filters, because it was computed at refresh time, before any visual existed. A measure cannot see 'the current row', because in a total row there isn't one. State those two sentences out loud before debugging and most problems resolve themselves.

The practical rule that falls out of that table: if the answer should change when someone clicks a slicer, it must be a measure. Writing it as a calculated column is not a slower way to get the same result — it is a different, frozen result.

'A single value cannot be determined' — the most useful error in DAX

This message means you referenced a column where DAX needed one scalar and got many. It usually appears when a measure written for a detail row is displayed on a subtotal, where the column holds several values and DAX refuses to pick one for you.

The wrong fix is to wrap the column in whatever aggregation silences it. MAX will make the error go away and quietly produce a number nobody can defend. The right fix is to decide what the subtotal means and aggregate to match.

-- Fails on any row that groups more than one product
Margin % = ( Sales[Price] - Sales[Cost] ) / Sales[Price]

-- Aggregate first, then divide: correct at every grain, including totals
Margin % =
DIVIDE(
    SUM( Sales[Price] ) - SUM( Sales[Cost] ),
    SUM( Sales[Price] )
)

-- When the per-row margin genuinely is the unit of interest,
-- iterate explicitly and say so:
Avg Row Margin % =
AVERAGEX(
    Sales,
    DIVIDE( Sales[Price] - Sales[Cost], Sales[Price] )
)

Relationships, joins and blends decide your numbers before any formula runs

Tableau's cannot blend secondary data source and Power BI's cannot determine relationships between the fields are the same complaint from two vendors: the model does not contain a path from the thing you are filtering to the thing you are measuring, at the grain you asked for.

This is worth getting right before anything else, because a wrong model produces plausible numbers rather than errors. A join that duplicates rows will silently double a sum; a relationship pointing the wrong way will make a filter do nothing at all. Choose deliberately:

Blending fails most often not because blending is fragile but because the linking field is dirty: trailing spaces, different casing, a code stored as text on one side and a number on the other. Clean the key first; the blend stops being temperamental.

Refresh failures are credentials and gateways, almost every time

A report that works on the desktop and fails on the service has almost never got a formula problem. The desktop used your Windows identity and your machine's network route; the service has neither. Power BI needs credentials stored on the dataset and, for anything on a private network, an on-premises data gateway with a matching data source definition. Tableau needs embedded credentials on the published data source and a running, correctly-versioned Bridge client for cloud-to-private refreshes.

The diagnostic order is fixed and saves hours: confirm the credential is stored (not just entered once), confirm the gateway or Bridge is online, confirm the data source definition on the gateway matches the server and database names in the file exactly — a difference as small as a hostname versus its fully-qualified form is enough to break the match.