Combine SELECTCOLUMNS and ORDER BY in DAX

Question

This works, it selects multiple columns:

evaluate SELECTCOLUMNS(branches,
         "The branch code", branches[code], 
           "The branch name", branches[name], 
           "The branch city", branches[city])

This also works:

evaluate branches
    order by branches[name]
    start at "Co"

But if I want to combine the two, I get an error:

evaluate ( SELECTCOLUMNS ( branches,
    "The branch code", branches[code],
    "The branch name", branches[name],
    "The branch city", branches[city]) )
    order by branches[name]
    start at "Co"

A single value for column 'name' in table 'branches' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The error talks about aggregation, but I don't need to aggregate. What is the problem and how to fix it?

RADO RADO · Accepted Answer · 2020-06-20T22:01:06

Function SELECTCOLUMNS returns a table, and ORDER BY refers to this new table, not to the original table "branches". Since new table does not contain field [name], you get the error. To fix it, simply refer to the fields in the new table:

EVALUATE
SELECTCOLUMNS (
    branches,
    "The branch code", branches[code],
    "The branch name", branches[name],
    "The branch city", branches[city]
)
ORDER BY [The branch name]
START AT "Co"

Combine SELECTCOLUMNS and ORDER BY in DAX

1 Answers