- ADDCOLUMNS is an iterator and a table function.
It receives first, the first argument
that needs to be a table.
ADDCOLUMNS returns the full table.
Then during the iteration, it can create new columns.
For each column that you want to add to the output,
you need to provide both the name of the column
and the expression.
The expression is evaluated in the row context
during the iteration and that is why if you want
to create a filter context out of the row context,
you need to rely on context transition
and use CALCULATE in order to compute your numbers.
Let's see that with a couple of examples.
You see that we are using, in this query, ADDCOLUMNS
that iterate over the values of the calendar year.
So for each year ADDCOLUMNS evaluates the further arguments.
We provide a name and then an expression
that is evaluated in the row context of ADDCOLUMNS.
This is why you can reference date calendar year,
as we are doing here, to extract the value
of a specific column.
The first column, year number,
only returns the digits of the year.
The second column, amount, computes the sales amount.
It is calling a measure, therefore CALCULATE is there,
context transition is happening
and the sales amount is the sales amount of the given year.
If you look at quantity wrong and quantity correct,
you see the need to use context transition.
In quantity wrong, we are only computing
SUM of sales quantity.
No context transition is happening,
therefore, because we only have a row context,
the value returned is the grand total of all quantities.
If you want ADDCOLUMNS to compute the value
in a filter context, you need to rely on CALCULATE
as we are doing here to compute the quantity correct.
One important note about ADDCOLUMNS
is about the data lineage.
ADDCOLUMNS does not keep the data lineage
of the new column that it creates.
So any new column that is generated by ADDCOLUMNS,
even though it is only a reference to one column
does not maintain the old lineage,
it creates always a new lineage.
The example is kind of tricky,
so we need to follow it closely.
Here I'm defining a first variable, year and year numbers,
that uses ADDCOLUMNS to add to the values
of the calendar year, a new column, year number,
which is the same as date calendar year
and the sales based on the year,
which is using context transition
and computes the sales amount.
The result is the calendar year, the year number
and then the sales based on the year.
The sales based on the year represent the sales
of the given year because of context transition.
The next step that I want to do
is to get rid of the first column, calendar year,
because the first column coming from the calendar year
has the correct lineage, it is a year.
The second column contains the very same value
but ADDCOLUMNS will have created a new lineage,
so it is not a calendar year.
This is what is happening in the second variable,
year numbers, uses SELECTCOLUMNS to select
from this table only the year number
and the sales based on year.
I'm getting rid of the first column.
And you see the result here.
It only contains year number
and sales based on year, which has not been recomputed
because it was computer earlier.
In the last step, I iterate over this table,
containing the year number and the sales based on year
and I compute again sales amount using context transition.
Now, this context transition should filter the year
because we have a year number
but because ADDCOLUMNS did not keep a lineage,
it created a new lineage, as you see in the result,
the sales based on year number computed in result contains
always the grand total of sales
and the reason is, ADDCOLUMNS did not maintain the lineage,
which is different than many other DAX functions
that keep the lineage if you're using a simple expression.