Introduction to DAX — Power BI
Data Analysis Expressions are a collection of functions that can be used to perform a task and return one or more values. Although this sounds very similar to any other programming language, DAX is only a formula or a query language. DAX was developed around 2009 by Microsoft to be used with Microsoft’s PowerPivot, which at that time was available as an Excel (2010) add-in. It is extremely popular today as it is now the language of choice for Power BI and is supported by Tabular SSAS as well. Since it is widely being used in Power BI, I have concentrated on DAX for Power BI in this article but, can be applied to other applicable tools.
Power BI is a Business Intelligence tool that consumes data from hundreds of data sources, preps the data and produces beautiful interactive reports and dashboards that deliver compelling insights. It provides a suite of services to bring the data in, build dashboards or reports and share it with the required folks. Although Power BI is relatively new, its core in a way has been in the making for a long time. It sits on the shoulders of SSAS (SQL Server Analysis Services), Power Pivot, Power View and Power Query — some of which were available as a free Excel add-in.
Within the entire suite of Power BI, DAX is mostly used to transform the data model imported into Power BI and/or to add new measures for visualizations. These functions are used to create Calculated Columns or Measures to help with reporting.
DAX is also used in securing the reports and dashboards that are published by providing Row-Level Security (RLS) at the data model level.
Calculated columns are created within the data model and are calculated row by row at the column level and are stored within the data model and become part of the table in which it resides. Calculated columns also consume more memory. A calculated column is usually represented as Table[Column].
Measures, as the name suggests are aggregates and DAX helps with creating additional measures based on the data model to help with reporting/visuals. These DAX functions run on the fly, also known as dynamic aggregation and hence are not stored with the data model. Since the measures are calculated at the time of query, they consume additional CPU. A measure is simply represented as [Measure].
DAX aggregation functions aggregate a column or expression over rows in a table and are used in Measures. DAX provides a lot of in-built aggregate functions and here’s a reference list.
The Average Sales measure would now return 500.
‘X’ Aggregation Functions
X aggregation functions iterate and aggregate over rows in a table, the same as an aggregate function — the only difference being X version of the aggregate function aggregates over an expression instead of column in a table.
Average Sales=AVERAGEX(Customer, [Sales] * [Quantity])
The Average Sales measure would now return 1433.34.
A named variable stores the result of an expression which can later be passed to other expressions as a parameter. Variables reduce the redundancy in the code and also help in performance by avoiding the need to create a new calculated column.
Time Intelligence Functions
Time Intelligence functions are extremely useful when it comes to Business Intelligence and also saves time in performing complex calculations in simple functions. These functions work on date and time fields to produce a wide array of calculations based on different levels of time period. Here’s a list of Time Intelligence functions.
Text functions work on columns in a table to concatenate, search or manipulate the string and return the whole or a part of a string. Here’s a list of text functions.
Table functions return full tables instead of values. They are typically used in conjunction with other functions to iterate over each row in the result table returned.
Imagine you have a group of related tables and need to perform calculations based on two or more tables. Some of the table functions are most useful in such cases. Here’s a list of table functions.
The function FILTER returns a subset of the current table based on the expression provided and returns a table as a result.
SuperStore = FILTER (Orders, Orders[Product Name] = “Newell 317”)
This creates a new table by the name SuperStore with just Newell 317 products in them as shown below.
The function RELATEDTABLE returns all the rows in the table that are related to the current table.
Count of Orders= COUNTROWS(RELATEDTABLE(‘SuperStore’))
While this is just a brief introduction to DAX, there are a ton of other useful functions in DAX that will make the life of an analyst or a developer a lot easier.
Here are some excellent resources to learn DAX