The Simple DAX Functions as SELECT Statement

Do you even wonder how to use DAX function to query BISM to return a simple dataset or a dataset that is grouped by some attributes? 

Evaluate is the core DAX function that returns a table of data. It is similar to the SELECT statement in T-SQL.

Here is the Syntax:

EVALUATE <table>  

Example:

We want to select all the data from the Internal Sales table.

EVALUATE('Internet Sales')

By using the Evaluate function, the query returns all the rows and columns from the Internet Sales as below:




To sort the result,we could add ORDER BY at end. For example, we want to sort the result by Customer Id and product ID, we could do the following:




CalculateTable is a function that return a table that modified by the giving filters. It is similar to the SELECT * statement with WHERE Clause in T-SQL

Here is the Syntax:

CALCULATETABLE(<expression>,<filter1>,<filter2>,…)

This function takes expression for a table as the first parameter and any number of Boolean expressions as filter.

Example:

We want to return the Internal Sales records that were ordered in 2007 and the [Product Category] equals to “Bikes”.

EVALUATE

CALCULATETABLE(

 'Internet Sales',

 'Date'[Calendar Year] = "2007", 

 'Product Category'[Product Category Name] ="Bikes" 

)

ORDER BY 'Internet Sales'[Order Date]

By using the CalculateTable function, the query returns the records that match the filters provided.

Summarize is a DAX function that returns a table for the requested totals over a set of groups. It is similar to the SELECT statement with Group By in T-SQL.

Here is the Syntax:

SUMMARIZE(<table>, 

<groupBy_columnName>[, <groupBy_columnName>]…[, <name>, 

<expression>]…)

This function takes table of data as the first parameter, any number of columns as group by parameter, the name given to the Sum of the column, and the expression

Example:

We want to Sum up the total of [Internet Total Sales], total of [Internet Total Tax Amt], and total of [Internet Total Margin] FROM the [Internet Sales] table grouped by the [Calendar Year] and the [Product Category].

EVALUATE

SUMMARIZE('Internet Sales',             
    'Date'[Calendar Year],'Product Category'[Product Category Name],             
    "Total Sales", 'Internet Sales'[Internet Total Sales],            
    "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],            
    "Total Margin", 'Internet Sales'[Internet Total Margin]            
)ORDER BY 'Date'[Calendar Year],'Product Category'[Product Category Name]

By using the Summarize function, the query returns the sum of the measures that are grouped by the columns provided.

Reference:

http://msdn.microsoft.com/en-us/library/gg492156.aspx

http://msdn.microsoft.com/en-us/library/ee634760(v=sql.105).aspx

http://technet.microsoft.com/en-us/library/gg492171.aspx