DAX DEFINE returns column not found error

Question

This works:

EVALUATE
ADDCOLUMNS(
    FILTER (Sales, [StoreKey] = 155 ) ,
    "Margin", (Sales[SalesAmount] - Sales[TotalCost])
)

However, if I try to define a function, I get an error:

DEFINE VAR Margin = Sales[SalesAmount] - Sales[TotalCost]
EVALUATE
ADDCOLUMNS(
    FILTER (Sales, [StoreKey] = 155) ,
    "Margin", Margin
)

Query (1, 21) A single value for column 'SalesAmount' in table 'Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

What is this error and how to fix it?

RADO RADO · Accepted Answer · 2020-07-04T16:25:41

There are 2 problems with the second code:

It's missing Row Context
VAR is not a function, it's a variable. Functions in DAX are Measures

What is "Row Context"? In short, in Power BI data is stored in a database. When you are referring to a column in the database, you must either: aggregate the data in it (i.e., sum it), or provide a specific row ("row context").

In your first code, function ADDCOLUMNS is an iterator. It means that it loops a table row by row, and for each record does a calculation. Since during each iteration it "knows" which row it's on, you can refer to the table fields without problems.

In the second code, this line:

Margin = Sales[SalesAmount] - Sales[TotalCost]

has no row context - it does not know which record to use for the calculation, and hence the error. You must either aggregate the data first, or put this calculation inside an iterator.

In this particular case, the simplest solution is to use aggregation:

DEFINE
    MEASURE 'Sales'Margin = SUM ( Sales[SalesAmount] ) - SUM ( Sales[TotalCost] )
EVALUATE
ADDCOLUMNS (
    CALCULATETABLE ( Sales, Sales[StoreKey] = 155 ),
    "Margin", [Margin]
)

Here, we first aggregate amounts and costs and calculate margin. Then, inside of ADDCOLUMNS we iterate table Sales filtered for a specific store, and for each row we call the measure defined above.

If you need to use an iterator instead of aggregation, you can do something like this:

DEFINE
    MEASURE 'Sales'Margin = SUMX(Sales, Sales[SalesAmount] - Sales[TotalCost] )
EVALUATE
ADDCOLUMNS (
    CALCULATETABLE ( Sales, Sales[StoreKey] = 155 ),
    "Margin", [Margin]
)

DAX DEFINE returns column not found error

1 Answers