DAX vs MDX – Is there any difference?
To state the obvious, both DAX vs MDX have their own purposes to be able to co-exist. MDX can be used to query data from a cube and do a pivot of that data while DAX, being more of an expressions language than a query language, cannot be used similarly.
Hence before we look at the differences, we must understand their purpose. Both being expression language, MDX and DAX are designed to query an SSAS Cube. The difference, however, lies in the details. MDX can be used to query against SSAS Multi-dimensional models, while DAX is used for SSAS Tabular models. If you use Power BI for your visualization needs, it can connect to SSAS Multi-dimensional models even though Power BI primarily uses Tabular models.
DAX vs MDX – 3 Differences
- DAX is easy to learn as it as the Expression language was based on the Excel Expressions. So, anyone with a sound knowledge of Excel can easily learn DAX. MDX, on the other hand, is designed on Multi-Dimensional notion where you will be able to run a query against a cube that outputs measures and dimensions
- DAX supports Direct Queries while MDX doesn’t allow this to be done
- DAX doesn’t support calculated members and does not allow named sets. MDX is designed to help scopes and assignments
You will find it more comfortable and be in familiar territory while using aggregating and filtering concepts in DAX if you come from a SQL background. You might find the MDX expressions a bit complex comparatively.
Will DAX Kill MDX?
Actually, NO. DAX might be termed as the future, but MDX isn’t going away anywhere in the least foreseeable future.
Both of them have their own use cases where one over the other can be used. Imagine DAX as an extension of the Excel formulas that can be used to calculate against a PowerPivot dataset.
DAX also supports many MDX functions like TotalYTD, ParallelPeriod, to name a couple.
When to Use What?
Data stored in a database: You will typically use SQL for querying data and then import the result set to an Excel worksheet and finally move on to using Excel expressions against this data to enhance it.
Cube Data: MDX query is the obvious choice here as you can use Excel pivot to query and import the result sets into excel worksheet.
PowerPivot Data: You could use PowerPivot to retrieve the data, load into Excel, and finally used DAX to perform calculations.
I hope this post gives you a fair understanding of what DAX and MDX are, their similarity and differences, which you can take back, and try it out against your data sets.
