SQL Server BI Semantic Model for Understanding DAX
DAX Query Part 1
We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.
This post comes as a supplementary addition to the earlier post which describes the COUNT Function.
In this post let us write out first DAX query. Fairly easy!
Please note that I am using the AdventureWorksDWDenali as my database.
EVALUATE() : Evaluate the context which is passed as an argument and return a table
evaluate ( FILTER(DimCustomer, DimCustomer[MaritalStatus]="S") )
This DAX Query returns all customers who are not married. Here the current context to evaluate is the FILTER
SUMMARIZE(): As the same suggests, this function is used to summarize the data. Perform aggregation and any grouping as required. So the result set in this case a table is not “flat or detailed” but grouped data set which is determined by the group expression passed as the argument.
- The header names are fully qualified, so the header names of all columns, either from base table or the calculated columns should be named while building the tabular model.
- *In SSMS, you cannot create calculated columns and use them in subsequent queries.
- All calculated columns thus should be created while building the tabular project and later used for analysis.
- Apparently, CONCATENATE() function does not works in SSMS for DAX Queries. But it yields correct result when used in Excel or Visual Studio for BIDS. So one cannot perform a concatenation operation on columns in SSMS
evaluate ( Addcolumns ( DimCustomer ,"Full Name",([FirstName] & " " & [LastName]) ) )
This DAX query will create a new column in the DimCustomer Table
*But this column cannot be referenced in the subsequent queries within SSMS.
Please Note: * means I am still evaluating this feature
That’s all. Happy Learning!