How to write SELECT for Analysis Services Tabular using DAX

DAX, Data Analysis Expression is a language that can be used with Microsoft Analysis Services tabular data models. It was initially introduced with Power Pivot Excel Add-In and later it was added to Analysis Services tabular models. DAX language holds collection of functions, operators and constants that can be used for creating calculated columns and measures, extending tabular models.

Since we always write queries with SELECT with database engine and Analysis Services multi-dimensional model, requirement for writing the same is not uncommon with tabular model too. How can we write a SELECT query with DAX?

Retrieving data from tabular model tables can be done with EVALUATE keyword, which is functionality equivalent to SELECT statement. Although it is not as rich as TSQL SELECT, it can be used for getting data for general reports.

Here are some examples;

-- Getting data from Internet Sales
evaluate ('Internet Sales');

-- With filtering
evaluate(Filter('Internet Sales', 'Internet Sales'[Product Standard Cost] > 1000));

-- With ordering
evaluate(Filter('Internet Sales', 'Internet Sales'[Product Standard Cost] > 1000))
order by 'Internet Sales'[Order Date] desc;