SQL Server BI Semantic Model for Understanding DAX

SQL Server BI Semantic Model for Understanding DAX

DAX Query Part 1

Hello!

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.

Notes:

1. 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.
2. *In SSMS, you cannot create calculated columns and use them in subsequent queries.
3. All calculated columns thus should be created while building the tabular project and later used for analysis.
4. 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!

Regards

Raunak Jhawar

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook