I had an issue in a query that reminded me a few details naming columns created by ADDCOLUMNS.

When you specify a simple name in ADDCOLUMNS, you can use it later, for example in a SUMMARIZE or in a FILTER expression, such as in the following example (see the Value column):

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( ‘Date'[Date] ),
            “Value”, [Internet Total Sales]
        ),
        “Frequency”, COUNTROWS (
            FILTER (
                VALUES ( ‘Date'[Date] ),
                [Internet Total Sales] <= [Value]
            )
        )
    ),
    ‘Date'[Calendar Year] = 2006,
    ‘Date'[Month] = 7
)

Naming an added column in this way makes it accessible only through its name, using the “measure” syntax, which doesn’t have a table name before the name of the column (please, remind that this is not a best practice – always use the table name before the column name when you reference a column and always omit the table name when you reference a measure!).

So, a better way to write the previous expression could be the following one:

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( ‘Date'[Date] ),
            “’Date’[Value]”, [Internet Total Sales]
        ),
        “Frequency”, COUNTROWS (
            FILTER (
                VALUES ( ‘Date'[Date] ),
                [Internet Total Sales] <= ‘Date’[Value]
            )
        )
    ),
    ‘Date'[Calendar Year] = 2006,
    ‘Date'[Month] = 7
)

Until now, there is nothing really new. It could be just a good reminder. Now, for some reason a few days ago I wrote this:

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( ‘Date'[Date] ),
            “[Value]”, [Internet Total Sales]
        ),
        “Frequency”, COUNTROWS (
            FILTER (
                VALUES ( ‘Date'[Date] ),
                [Internet Total Sales] <= [Value]
            )
        )
    ),
    ‘Date'[Calendar Year] = 2006,
    ‘Date'[Month] = 7
)

In this case executing the query you receive an error that says that the Value column is not found. The problem is that I used “[Value]” instead of  “Value”. So the lesson is: be careful when you name a column, don’t use square brackets unless you want to include the table name, which is a good idea so you will not confuse its semantic with a measure when you reference such a column later in your expression.

ADDCOLUMNS

Returns a table with new columns specified by the DAX expressions.

ADDCOLUMNS ( <Table>, <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )

SUMMARIZE

Creates a summary of the input table grouped by the specified columns.

SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )

FILTER

Returns a table that has been filtered.

FILTER ( <Table>, <FilterExpression> )

CALCULATETABLE
Context transition

Evaluates a table expression in a context modified by filters.

CALCULATETABLE ( <Table> [, <Filter> [, <Filter> [, … ] ] ] )

VALUES

When a column name is given, returns a single column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row if present.

VALUES ( <TableNameOrColumnName> )

COUNTROWS

Counts the number of rows in a table.

COUNTROWS ( <Table> )