The solution based on SUMMARIZE is better from a performance point of view, but it is interesting also to look at a possible solution based on ADDCOLUMNS for educational purposes.

Let’s start with the classical SELECT in SQL:

FROM Product

It corresponds to this DAX query:


Now, a common projection consists in selecting just a few columns from the source table. For example, the following SQL query only gets 3 rows from Product table.

SELECT [Product Id], [Product Name], [List Price]
FROM Product

In DAX you can obtain the same result in two ways. You can leverage on the uniqueness of the Product Id column, which uniquely identify any row in the Product table, and you can query such column adding the other columns by using the ADDCOLUMN function. In order to retrieve the corresponding value for each product, you can use CALCULATE and VALUES (so that you will get an error in case Product Id wouldn’t be a unique key of the table).

    DISTINCT( Product[Product Id] ),
    "Product Name", CALCULATE( VALUES( Product[Product Name] ) ),
    "List Price", CALCULATE( VALUES( Product[List Price] ) )

The technique based on ADDCOLUMNS allows you to rename the columns after the first one. However, from the point of view of performance and easiness of read, a better solution is the one based on SUMMARIZE, even if you lose the ability to rename columns in this way.

    Product[Product Id], 
    Product[Product Name],
    Product[List Price]

Technically, the solution based on SUMMARIZE is equivalent to a SELECT DISTINCT statement like the following one:

SELECT DISTINCT [Product Id], [Product Name], [List Price]
FROM Product

In general, I always suggest using SUMMARIZE in order to perform a projection over a table, because it is easier to read and it has better performance than other options you have in DAX.

Articles in the From SQL to DAX series