This article will introduce you to DAX, its functions, and the best resources for learning it. What does DAX stand for? DAX is short for Data Analysis Expressions. It is a functional formula language that defines calculations for data modelling and reporting. DAX query language was originally released with Power Pivot in Excel back in 2010; it now also interacts with data in Microsoft Power BI and SSAS (SQL Server Analysis Services) tabular models.
As a data analytics tool, Power BI is perfectly capable of generating valuable insights without the user knowing any DAX formulas. However, for more complex calculations like defining year-over-year growth versus the market’s performance or comparing product categories over multiple date ranges, DAX formulas are indispensable. As any experienced user will soon realize, DAX is essential for getting the most out of Power BI.
Key differences between DAX and Excel functions
There are over 250 DAX functions or “expressions”, some of which mirror those found in Excel and others that aggregate and draw relationships between data in more advanced ways. As well as offering additional functions, DAX supports more data types than Excel.
Data types available in DAX include integers, decimals, dates and times, currencies, boolean values, strings (text), binaries (e.g. files or images), and blanks. Blanks are particularly useful as they enable users to replace null values with a more natural result such as “0”, therefore reducing errors.
Although the syntax in DAX and Excel is quite similar, there is one key difference: Rather than applying formulas to cell references and ranges, as in Excel, DAX works with tables and their columns. For example, in Excel you might use “= SUM (C1:C100)”, whereas in DAX you would use “= SUM (Table name[column name])”. This ultimately makes DAX more intuitive as it saves searching for the relevant cell numbers and utilizes natural language instead. DAX is also capable of autocompleting table and column names as you type, and prevents you from entering anything that cannot be used in your calculation.
DAX expressions can be written in two different forms: calculated columns and measures. Similar to Excel formulas, calculated columns are pre-calculated and stored in memory. They reside in the data tab as added columns with calculations returned for every row.
Measures, on the other hand, deliver dynamic results. They are stored as formulas that can be executed on the fly by dragging them into a report page. Measures can be filtered using slicers and are typically used for aggregation to return one single value. They do not consume storage space or cause the slow performance often experienced when multiple calculated columns are present.
The benefits of learning DAX
In addition to the advantages over Excel functions outlined above, there are many reasons to learn DAX. As we have already mentioned, DAX is highly versatile. Unlike M, which is used only for Power Query, DAX can be used in multiple programmes, including Power Pivot in Excel, Power BI, and SSAS.
In Power BI, DAX supercharges reports and dashboards by enabling dynamically generated measures and tables from user input. The use of succinct DAX expressions can also speed up your dashboards by reducing the amount of data that needs to be loaded and analysed.
Where to start learning DAX
When starting out in DAX, it is important to understand the theory behind it. According to SQLBI, there are four foundational concepts to master:
- Evaluation contexts
- Iterators
- Context transition
- Expanded tables
The other challenge is adjusting your mindset to start thinking in terms of columns and tables, rather than individual cells. The good news is, if you are experienced with Excel, you know the power of formulas and functions like IF statements and VLOOKUP. DAX takes Excel formulas to the next level. This knowledge will help you in understanding DAX.
If you’re wondering how to learn DAX, here are some useful resources to help you get you started:
- Microsoft Power BI User Group in Melbourne: Around the world, many cities are home to Power BI user groups that host tutorials, discussions, and knowledge-sharing sessions. One example is the Melbourne Meetup group, which is highly active and hosts monthly events led by experts in the field. More recently, these events have gone virtual so participants can attend from anywhere.
- Power BI community: Microsoft’s online community provides another great opportunity to connect with and learn from business intelligence experts and peers. Here, users can post specific queries about DAX and other elements of Power BI.
- Official DAX documentation: Microsoft offers a comprehensive online guide to DAX, including its operators, functions, queries and syntax.
- Guy in a Cube YouTube channel: independently hosted by two experienced Microsoft employees, this channel helps viewers master Microsoft business analytics in Power BI, Reporting Services, Analysis Services and Excel. Head here for live-streamed Q&A sessions, informative round-ups, and technical tutorials.
- SQLBI articles: Launched in 2004, this website has become a goldmine of free BI resources, including articles, videos, training, and DAX guides.
Learn DAX from the experts
As Australia’s leading Power BI specialist and a Microsoft Gold Partner in Data Analytics, DWC can provide one-on-one mentoring and private DAX language training for your organisation, both in person and remotely. Contact us today to find out more.
