{"id":1752,"date":"2021-01-11T08:00:00","date_gmt":"2021-01-11T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1752"},"modified":"2024-09-11T21:34:18","modified_gmt":"2024-09-11T18:34:18","slug":"ti-diafores-echei-i-mdx-me-tin-dax","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-diaphores-echei-i-mdx-me-tin-dax\/","title":{"rendered":"What are the differences between MDX and DAX queries?"},"content":{"rendered":"

They are both expression languages used for data analysis in Power BI, Excel and Analysis Services.<\/p>\n\n\n\n

If we would like to give an answer to our question with a sentence, h MDX (Multidimensional Expressions<\/em>)<\/strong> you use to analyze multidimensional cubes while h DAX (Data Analysis Expressions) <\/strong>use to analyze tabular models in one Data Warehouse<\/strong>. <\/p>\n\n\n\n

Their differences in detail<\/h2>\n\n\n\n

THE DAX<\/strong> in filtering and aggregation (SUM, COUNT, MAX, etc.) it reminds the logic of SQL. Also in the expressions they are compiled similarly to Excel (e.g. SUM('Internet Sales'[Sales Amount])<\/em>). Allows use DirectQuery<\/strong> to access hot data. It performs better than MDX<\/strong>. It does not support the concept of calculated members and named sets provided by MDX<\/strong>.<\/p>\n\n\n\n

On the other hand, MDX<\/strong> uses the dimensions of the multidimensional cube in axes (up to 128) to display the data. It is easier to write. Needed to extract data for data mining.<\/p>\n\n\n\n

Example in their different syntax<\/h2>\n\n\n\n

Let's say we want to pivot with internet sales for the year 2019 and 2020 like below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

MDX<\/h4>\n\n\n\n

Using MDX<\/strong> we should declare on each axis the information we want to see. In this example, we want to see "Sales" on the first axis and on the second axis for the years 2019 and 2020.<\/p>\n\n\n\n

SELECT\n\n{ [Measures].[internet Sales Amount] } ON AXIS (0),\n{ [Date].[Calendar Year].[2019],\n[Date]. [Calendar Year].[2020] } ON AXIS (1)\n\nFROM\n[Adventure Works]<\/pre>\n\n\n\n
DAX<\/h4>\n\n\n\n
Using DAX<\/strong>  one should be created Measure<\/strong> (measurement) by using it Aggregate Function \u201cSUM\u201d<\/strong> so that we pivot to the year 2019 and 2020.<\/p>\n\n\n\n
DEFINE\nMEASURE 'Internet Sales'[internet Sales Amount] = SUM('Internet Sales'[Sales Amount])\nEVALUATE\nSUMMARIZECOLUMNS\n(\n    'Date'[Calendar Year],\n    TREATAS({2019, 2020}, 'Date'[Calendar Year]),\n    \"Internet Sales\", [internet Sales Amount],\nALLSELECTED('Date'[Calendar Year]))\n)\nORDER BY [Calendar Year]<\/pre>\n\n\n\n
Which one is better?<\/h2>\n\n\n\n
Of course there is no answer to this question.<\/p>\n\n\n\n
Depending on the use we want to make we also choose the language, for data in a database we will use SQL, for data in a multidimensional cube MDX and for a PowerPivot \/ Tabular DAX models.<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n