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”.
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
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:
,"Internet Sales",'Internet Sales'[Internet Total Sales])
ORDER BY 'Currency'[CurrencyName], 'Customer'[Customer Id]
'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 BYis 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 Convert DAX to MDX, Convert MDX to DAX, DAX function, EVALUATE, EVALUATE IN DAX, Example of Evaluate, Example of Summarize, MDX to DAX, SUMMARIZE, SUMMARIZE in DAX, Understand DAX. Bookmark the permalink. Leave a comment.