What are the differences between MDX and DAX queries?
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
They are both expression languages used for data analysis in Power BI, Excel and Analysis Services.
If we would like to give an answer to our question with a sentence, h MDX (Multidimensional Expressions) you use to analyze multidimensional cubes while h DAX (Data Analysis Expressions) use to analyze tabular models in one Data Warehouse.
Their differences in detail
THE DAX 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])). Allows use DirectQuery to access hot data. It performs better than MDX. It does not support the concept of calculated members and named sets provided by MDX.
On the other hand, MDX 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.
Example in their different syntax
Let's say we want to pivot with internet sales for the year 2019 and 2020 like below.
MDX
Using MDX 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.
SELECT
{ [Measures].[internet Sales Amount] } ON AXIS (0),
{ [Date].[Calendar Year].[2019],
[Date]. [Calendar Year].[2020] } ON AXIS (1)
FROM
[Adventure Works]
DAX
Using DAX one should be created Measure (measurement) by using it Aggregate Function “SUM” so that we pivot to the year 2019 and 2020.
DEFINE
MEASURE 'Internet Sales'[internet Sales Amount] = SUM('Internet Sales'[Sales Amount])
EVALUATE
SUMMARIZECOLUMNS
(
'Date'[Calendar Year],
TREATAS({2019, 2020}, 'Date'[Calendar Year]),
"Internet Sales", [internet Sales Amount],
ALLSELECTED('Date'[Calendar Year]))
)
ORDER BY [Calendar Year]
Which one is better?
Of course there is no answer to this question.
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.
