Introduction – What is DAX?
Data Analysis Expression (DAX) is a formula language used in Microsoft Power BI. DAX formulas include functions, operators and values. Formulas allow users to define customised calculations and queries on data in related tables and columns.
End users were previously limited to Microsoft Excel functions, and DAX gives users more control and possibilities over their own data analysis. DAX was initially developed in 2009 by Microsoft to be used within Power BI alongside Microsoft SSAS Tabular and Microsoft PowerPivot (which at the time was an Excel 2010 add-in.)
Data Analysis Expression formulas can be used across different software programs. But we’re here to give you a brief overview of DAX in Power BI, some of its functions and what makes DAX unique.
For your reference, in our previous blog we examined the September 2019 updates to Power BI Desktop – which did include new DAX expressions.
Microsoft Power BI is a business intelligence platform that is used to link, analyse and visualise large sets of data. Power BI allows users to create dashboards and business intelligence solutions that help them see the progress of their business. Power BI’s purpose is to make data easy to understand and ease the decision-making process.
DAX is used in Power BI to perform calculations and data analysis queries.
To understand DAX formulas in Power BI you should examine three main concepts; Syntax, Context and Functions.
- Syntax – consisting of the various elements that make a formula, i.e. how the formula is written. Similarly to Excel, DAX formulas start with an “=” sign preceding the function or expression.
- Context – consisting of row context and filter context, applied whenever formula applies filters to identify single rows, and calculations that determine results, respectively.
- Functions – predefined formulas that perform calculations by using specific values, which we’ll examine below.
For more information on our Power BI consulting services, click here.
Did you know?
DAX can also be used to control conditional formatting of cells in Power BI Desktop. Background colour scales, font colour scales and data bars are options for conditional formatting. You can create a DAX calculation based on conditions. Using the SWITCH function in the ColorKPI field, you can set field values for any of these options using DAX, and sometimes is preferred than entering multiple conditional formatting options.
With DAX, there are a number of functions that can be used to analyse or shape your data. The main functions can be categorised from the following:
- Filter – functions that help you return specific data types, filter by values and look up values in related tables.
E.g. CALCULATE – this function evaluates an expression in a context that is modified by specified filters.
- Statistical – functions that can perform aggregations (similar to Microsoft Excel)
E.g. AVERAGE – this function returns the average of all numbers in a column.
- Logical – functions that return information about values in an expression.
E.g. IF – these types of functions checks if a condition provided as the first ‘argument’ is met.
- Text – functions that allow you to return part of a string.
E.g. SUBSTITUTE – replaces existing text with new text in a string.
- Date & Time – akin to Excel’s date & time functions, but based on data types used in Microsoft SQL Server.
E.g. CALENDAR – returns a table with a single column named ‘Date’, and adjoining sets of dates.
- Information – these look at tables or columns provided as an argument to another function and tells you whether the value matches the expected type.
E.g. ISBLANK – this function checks any blank values and lists either TRUE or FALSE.
- Time Intelligence – these functions create calculations that use built-in calendar and date knowledge.
E.g. FIRSTDATE – this returns the first date in the context for the specified column.
Although some of the functions you might be familiar with, given that they are quite common in Microsoft Excel, DAX functions are unique in several ways.
- A DAX function always references complete columns or tables.
- Filters can be added to formulas for particular table or column values.
- DAX has functions that let you use row values (or related values) as arguments, to perform calculations that vary by context.
- Time Intelligence functions enable data to be manipulated using time periods, days, months, years and subsequently compare calculations.
- DAX possesses relational functions which allow you to interact with tables.
- DAX allows you to pass tables between expressions in Power BI.
Why is DAX useful?
DAX gives you the ability to create powerful formulas using a variety of different functions. With the possibilities that exist in Power BI, understanding DAX formulas will help you progress to quickly solve problems, and make very important decisions. From something as vital as the CALCULATE function to being able to compare annual growth across varying product categories, DAX possibilities are endless. Understanding DAX can give you comprehensive analysis strength you didn’t know you had.
Looking for some Power BI training?
XL Intelligence provide Power BI training services for your business. We run a standard Power BI course, or a more tailored option to suit your needs. Our universal Power BI training courses are suitable for anyone who wishes to use Microsoft Power BI at a more general level, where you can develop your knowledge further. For more information, reach out to us to see how we can help.