From SQL to DAX: Projection

Using DAX as a query language you will lack a fundamental feature like projection. In fact, DAX allows you add columns to an existing table, but you cannot simply remove some column from a table. In order to do that, you have to use SUMMARIZE, or you can use ADDCOLUMNS starting from a column that you want to include in the query output and that has to be unique for each row of the result.

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:

SELECT *
FROM Product

It corresponds to this DAX query:

EVALUATE Product

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).

EVALUATE
ADDCOLUMNS(
    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.

EVALUATE
SUMMARIZE(
    Product,
    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.