Convert MDX into DAX Part1

As we know DAX and MDX expressions can be used to query Tabular Model in Analysis services. Keep in mind that DAX does not work in SSAS Multidimensional Cube for querying data. We will understand basics of producing results from DAX same as MDX. DAX is promisingly giving quick result than MDX.  So if you are using Tabular model cube solution, then prefer writing DAX unless a complex logic came up which can only be resolved through MDX.

Let’s start with simple functions and queries to understand behaviour of DAX in terms of SQL as well as MDX.

Behaviour of DAX expressions:

  • DAX is working as a SQL language with difference of functions and syntaxes
  • We need not to write JOINS and GROUP BY clauses in DAX as it already understand that the purpose is to retrieve data in this form. Relationships among the tables of Tabular Model/ Power Pivot model are used for getting JOINS in the DAX query execution.
  • We need to understand that DAX is not a query language, it’s a query expression. So we are using some table expressions in DAX to retrieve data in table format and further it is used as result-set/ dataset for the SSRS report.
  • To evaluate DAX expressions use MDX query pane in SSMS


  • First step to start with DAX Query Reference. This function can be used to get the data in result set from a table.

    EVALUATE <<Tabular Expression or Name of the table>>

    Argument to the Evaluate function can only be a table name or any expression that return a table. Evaluate   function is added up with different other functions (like VALUES, SUMMARIZE, ADDCOLUMNS, TOPN etc.) to produce different results as per requirements.

    e.g.  Get table data of Employee table in “AdventureWorks Tabular Model SQL 2012”.

    EVALUATE 'Employee'

    As per behaviour of DAX, this is similar to SQL as:

    SELECT * from [Employee]

    We cannot compare here MDX for this DAX, as there is no way of getting table level attributes in MDX. We need to specify attributes required in MDX SELECT statement.


  • Function return required totals and counts, based on several groups (of different columns or say attributes from different tables in Tabular Model).

    We use SUMMARIZE along with EVALUATE for getting desired result in SSMS.

    SUMMARIZE (<<Table>>, <<Grouping Column1>>, <<Grouping Column2>>,,,<<Name for desired Aggregation1>>, <<Expression for Aggregation or Calculated MeasureName1>>, <<Name for desired Aggregation2>>, <<Expression for Aggregation or Calculated MeasureName2>>)

    Although it looks like a complex syntax but use is very simple and there are so many things we can do with this function.

    E.g. Let say you want to see Currency, Customer ID and Internet Sales from Adventure Works tabular model. Here a SQL query on database can be like:

    SELECT [CurrencyName], [CustomerID],SUM([InternetTotalSales])
    FROM [InternetSales] A
    LEFT JOIN Currency B ON B.CurrencyID = A.CurrencyID
    LEFT JOIN Customer C ON C.CustomerID = A.CustomerID
    GROUP BY [CurrencyName], [CustomerID]

    Easy, but in MDX it can be as:


    [Customer].[Customer Id].[Customer Id].MEMBERS ON 1
    ,[Measures].[Internet Total Sales] ON 0
    FROM [AdventureWorks_Test]

    Again easy.

    But in DAX, don’t worry it’s again easy. We need to keep in mind SUMMARIZE will need first Primary table (more of table which can be base of relationships among desired columns from different tables). Mostly these primary tables can be fact table, which relates different tables. Other than that JOINS and GROUB BYs are not required as SUMMARIZE take care of creating relationships and aggregating data for you.

    DAX for same situation is:

    SUMMARIZE('Internet Sales'
    ,'Customer'[Customer Id]
    ,"Internet Sales",'Internet Sales'[Internet Total Sales])
    ORDER BY 'Currency'[CurrencyName], 'Customer'[Customer Id]

    Here 'Internet Sales'[Internet Total Sales] is already a calculated measure, so Name for Measure is given as "Internet Sales". We can replace it as SUM([InternetTotalSales]), which is aggregation that we need.

    ORDER BY is same as SQL ORDER BY.

Most of the complex queries in DAX revolve around SUMMARIZE, so it’s important to understand its basics.


Posted on July 25, 2012, in DAX, MDX, SSRS Reporting and tagged , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: