In this series of blog posts, we will be examining some of the key features of Power BI Desktop, Power Query and the Power BI Service needed to pass exam DA-100: Analyzing Data with Microsoft Power BI.
The series was originally mapped to the MCSA: BI Reporting; however, the topics covered are also relevant to the newer exam DA-100.
In this topic, we will examine the use of DAX “X” functionis.
The DAX language contains a series of functions which are clones of standard statistical functions, such as SUM and AVERAGE, but which end in an X; for example, SUMX and AVERAGEX.
The standard version of these versions, without the “X”, can only take a literal column reference as the single argument; and can, therefore, only be used to aggregate a single column.
By contrast, the version of the functions which end in “X” take two arguments: a table and an expression. They then iterate through every row of the table, evaluating the expression, and then aggregate all the results.
Thus to calculate Units Sold, we can simply use the SUM function; since units sold is just the sum of the Quantity column.
However, to calculate the total Revenue, we need SUMX, since we must multiply Quantity by Units Sold then subtract one minus Discount.
The SUMX function iterates through the rows of the Sales table and computes the expression Sales[Quantity] * Sales[Unit Price] * (1 – Sales[Discount]).
The result of each computation is stored in the rows of a virtual column, in memory, and the result of the formula is the SUM of all the rows in this virtual column.
In our next example, we need to calculate the average time taken by clients to pay invoices.
To do this, we need to take the average difference between Invoice Date and Payment Date; we therefore need AVERAGEX rather than just AVERAGE.
The DATEDIFF function can be used to compute the difference between the Invoice Date and Payment Date columns.
The function takes three arguments: start date, end date and interval (the time units to be used; in our case DAY).
The AVERAGEX function iterates through the rows of the Sales table, using the DATEDIFF expression to compute the difference between invoice and payment date.
The result of each computation is stored in the rows of a virtual column, and the result of the formula is the AVERAGE of all the rows in this virtual column.