What is DAX?
DAX is a great language developed by Microsoft for querying data in tables. It is used in Power BI, Power Query in Excel, and SSAS Tabular Models.What can I do with DAX?
With DAX you can query data in three ways:- By creating measures that aggregate or sum up columns
- By creating calculated columns
- By creating calculated tables
Note that the calculated tables can be embedded into measures, effectively giving you the ability to do what would be called a subquery in SQL. That is, aggregating data into a dataset, against which you then base another query.
Comparison to Excel Formula Language and SQL
DAX is far more powerful than Excel formula language. Its ability to produce tabular datasets, which can then be queried themselves (i.e. subqueries) makes it more like SQL. But it resembles Excel formula language far more.
Basic Element of DAX
- DAX has variables, just like SQL. Declare them by writing VAR followed by the variable name.
- DAX has aggregation functions. These look like SUM and AVERAGE.
- DAX has relationships. These are accomplished in Power BI without the need for any JOIN statements at all. You simply connect the fields from multiple tables together, in a GUI.
- DAX has filters (the equivalent to WHERE clauses in SQL). These are accomplished by using the CALCULATE function.
- DAX can use subqueries (queries within queries). These can be used by either creating new calculated tables, and then basing a query off of that, or by using the SUMMERIZE within a DAX measure to do it all in one go.
Difficulties Learning DAX
- Learning what iterator functions do can be tricky. Effectively they do the same as creating a calculated column and then aggregating the created column.
- Learning why you can't input variables as parameters in certain functions. Note that variables are not calculated independently or before the RETURN statement. So the variable is calculated for every row of data you display. It does not return a single value for every row. It is calculated for every row you display in your visualisation, so may return different values for different rows.
- Learning why, sometimes, you need to perform an aggregation within a measure, rather than refer to another measure that performs the very same aggregation. Again, I have no idea why this is.
