The Acronym
DAX is an acronym and stands for Data Analysis Expressions. Moreover, it’s a language for data modelling, analysis etc. It has been developed by Microsoft and first released in 2009. It is the foundation for for data modelling amongst others in Power BI or Power Pivot in Excel.
Type & Functionality
It is a formulaic language which means you use it to build formulas and expressions with it. Since it is also a query language like SQL there is overlap in multiple functionalities. Both languanges can define things like joins, filters, aggregations, window functions, data transformations and more the like.
Data Analysis Expressions or DAX can also be seen as a toolbox or library of functions and operators. You can combine several functions and operators to build bespoke formular and expressions. What you use and combine depends on your analytic goals and your preferred approach.
Basic Syntax & common functions
CustomerName = NameTable[First Name] & “ “ & LastNameTable[Last Name]To explain what this command will do:
- CustomerName is the name of a new column which will be created after
- & expression is an operator to concatenate text
- “ “ is simply an empty string to separate first and last name with an empty space and not have it as one word
- [First Name] selects the column with the name First Name
So first we are looking in the NameTable for the first name and in the LastNameTable for the last name of each instance.
Second we concatenate first name and last name for each instance and store all newly concatenated names in the newly created column “Customer Name”. That’s basically it.
And last these are some common functions used in DAX:
- SUMMARIZE
- SUMMARIZECOLUMNS
- SELECTCOLUMNS
- FILTER
- UNION
- TOPN
- ADDCOLUMNS
- DATATABLE