Before we look into what are the differences between MDX and DAX, let’s understand their purpose. Both MDX and DAX are an expression based language designed to query an SSAS Cube. To keep things simple, MDX is used to query multi-dimensional SSAS models, whereas DAX is used for Tabular Data Models. However, in Visualization tools like PowerBI, which primarily uses Tabular Data Models, can also query Multi-Dim models too.
While on paper they might be simple expression languages, the code can get quite complex and lengthy. However, both of these uses inputs and gives you a calculated output for your analytical model. So, let’s jump in and understand the main highlights below:
Question: What are the Differences between MDX and DAX?
Answer:
- DAX is based on Excel-based Expression language with a lower learning curve. MDX is based on a multidimensional concept where you can query a Cube to get dimensions and measures as results.
- MDX cannot be used to Query Direct Query Models, whereas DAX can. At the time of release, DAX could not be used to query Multidimensional Models.
- There are no named sets or calculated members in DAX. You can use computed columns to create new fields in the data model. For MDX, it is designed for multidimensional functionalities like scopes and assignments.
- Advanced concepts like Parent-Child relationships, Many to Many, attribute relationships, key vs. Name etc. are possible in multi-dimensional concepts. DAX is mainly suited for model calculations and simple analytics. <Update> SQL Server 2016 onwards, most of the advanced MDX functionalities and bugs have been fixed.
If you are familiar with SQL more than the complex MDX expressions, the concept of using filtering and aggregating methods in DAX will be more familiar to you and your customers.
Also Read my Post on: What is DAX.