From SQL to DAX: Projection

2020-03-15 UPDATE: The original version of this article published in 2011 did not mention SELECTCOLUMNS, which was introduced in 2015. This article was rewritten in 2020 to provide updated coverage of projection functions in DAX.

The projection is provided by this classic SELECT in SQL:

SELECT *
FROM Product

It corresponds to this DAX query:

EVALUATE 'Product'

A common projection consists in selecting just a few columns from the source table. For example, the following SQL query only fetches 3 columns from the Product table:

SELECT [ProductKey], [Product Name], [Unit Price]
FROM Product

In DAX you can obtain the same result by using the SELECTCOLUMNS function, which requires you to specify the column name for each column of the result:

EVALUATE 
SELECTCOLUMNS (
    'Product',
    "ProductKey", 'Product'[ProductKey],
    "Product Name", 'Product'[Product Name],
    "Unit Price", 'Product'[Unit Price]
)

Another common projection in SQL adds one or more columns to all the columns of a table. For example, the following SQL query adds the Unit Margin column to all the columns of the Product table:

SELECT *, [Unit Price] - [Unit Cost] AS [Unit Margin]
FROM Product

You can get the same result in DAX by using the ADDCOLUMNS function:

EVALUATE 
ADDCOLUMNS (
    'Product',
    "Unit Margin", 'Product'[Unit Price] - 'Product'[Unit Cost]
)

Both SELECTCOLUMNS and ADDCOLUMNS keep the duplicated rows included in the original table expression. Applying a DISTINCT condition can be done in one of two ways. Consider the following SQL query:

SELECT DISTINCT [ProductKey], [Product Name], [Unit Price]
FROM Product

The more efficient way to get the same result is by using the SUMMARIZE function:

EVALUATE 
SUMMARIZE (
    'Product',
    'Product'[ProductKey],
    'Product'[Product Name],
    'Product'[Unit Price]
)

In case SUMMARIZE cannot be used over a complex table expression used as first argument instead of the simple Product table reference of this example, you could apply DISTINCT to the result of SELECTCOLUMNS . However, the following expression should only be used if SUMMARIZE cannot be used:

EVALUATE 
DISTINCT (
    SELECTCOLUMNS (
        'Product',
        "ProductKey", 'Product'[ProductKey],
        "Product Name", 'Product'[Product Name],
        "Unit Price", 'Product'[Unit Price]
    )
)

By using SUMMARIZE you cannot change the column names. If you need to rename a column it is advisable to use a SELECTCOLUMNS consuming the result of a SUMMARIZE, in order to achieve the best possible performance. For example, consider the following SQL query:

SELECT DISTINCT 
    [ProductKey] AS [Key], 
    [Product Name] AS [Name], 
    [Unit Price] AS [Price]
FROM Product

The corresponding DAX version is the following:

EVALUATE 
SELECTCOLUMNS (
    SUMMARIZE (
        'Product',
        'Product'[ProductKey],
        'Product'[Product Name],
        'Product'[Unit Price]
    ),
    "Key", 'Product'[ProductKey],
    "Name", 'Product'[Product Name],
    "Price", 'Product'[Unit Price]
)

Only if SUMMARIZE cannot be used, should you resort to this alternative, slower technique:

EVALUATE 
DISTINCT (
    SELECTCOLUMNS (
        'Product',
        "Key", 'Product'[ProductKey],
        "Name", 'Product'[Product Name],
        "Price", 'Product'[Unit Price]
    )
)

In general, we always suggest using SUMMARIZE as an equivalent of SELECT DISTINCT in SQL, whereas ADDCOLUMNS and SELECTCOLUMNS are the DAX functions to use to get a projection without removing duplicated rows in the result.