I recently published a video session on Project Botticelli web site regarding querying in DAX. You can write DAX queries to extract data from both PowerPivot and SSAS Tabular models. This could be particularly useful in three scenarios:
- Reports in Reporting Services
- Excel tables (used to populate other PowerPivot data models, for example)
- Reports embedded in custom applications (i.e. ASP.NET pages)
Today there is a lack of tools to help you writing a DAX query, probably the best choice is DAX Studio, which I used in the video, but it has to be enhanced and completed. I hope to be able to work on that, but remember that other volunteers are welcome in a Codeplex project!
In this video I cover in particular the syntax of EVALUATE, CALCULATETABLE, SUMMARIZE, ROLLUP, ADDCOLUMNS, and ROW functions. The video is available to Project Botticelli subscribers.
Evaluates a table expression in a context modified by filters.
CALCULATETABLE ( <Table> [, <Filter> [, <Filter> [, … ] ] ] )
Creates a summary of the input table grouped by the specified columns.
SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )
Identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate subtotals.
ROLLUP ( <GroupBy_ColumnName> [, <GroupBy_ColumnName> [, … ] ] )
Returns a table with new columns specified by the DAX expressions.
ADDCOLUMNS ( <Table>, <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )
Returns a single row table with new columns specified by the DAX expressions.
ROW ( <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )