Introduction tutorial to DAX using Xero Invoices and Power BI

This introduction tutorial will explain how to use DAX within Power BI and Excel.

It uses Xero Invoices and Power BI to explain DAX formulas using a concrete example. That being said, the concepts are generic. They work equally for MYOB data or any other data source.

What is DAX?

DAX stands for Data Analysis Expressions. It is a modelling language built into Power BI and Excel. Whereas Power Query downloads and shapes data, DAX calculates specific values or measures.

What is a Measure?

Measures represent a calculatable value.

But rather than calculating the value outright, a measure represents the steps or instructions to calculate a value within a context.

The actual values themselves will only get calculated if the measure is included in your chart or graph. Importantly, the chart or graph will determine the context.

As an example, let’s imagine a simple table which lists Xero invoices within Power BI.

Sample Invoices Data

With this table in mind, we want to create a single measure that calculates the ratio of INVOICES PAID. It will DIVIDE the PAID invoices by the TOTAL invoices.

PercentPaid by TypePercentPaid by Contact

The table on the left calculates this measure based on the Type of invoices: ACCREC and ACCPAY invoices.

The table on the right calculates this measure by Contact instead. Also notice the PercentPaid measure is recalculated on the Total Row. It is not a straight sum of the percentages above.

How to add a DAX measure in Power BI

The steps to add a DAX measure in Power BI are:

  1. Right-click on the Table in the list on the right-hand side.
  2. Select New Measure in the contextual menu.

or

  1. Select the Table in the list on the right-hand side.
  2. Select the Modelling tab in the ribbon above.
  3. Click New Measure.

How to add a DAX measure in Excel

The steps to add a DAX measure in Excel are:

  1. Select the Power Pivot table in the Excel ribbon.
  2. Click on the Manage button
  3. Enter the DAX formula in any cell

or

  1. Select your Pivot Table
  2. Go to the Pivot Table and Fields panel.
  3. Right-click on the Table in the list.
  4. Select Add Measure

Creating measures using the CALCULATE function

The most commonly used function in DAX is the CALCULATE function. It is required to convert any value into a measure.

In the example above, we created three separate DAX measures to calculate the values we needed.

  • TotalInvoicePaid
  • TotalInvoiceAmount
  • PercentPaid

We created three measures so as to break down the problem into three separately manageable steps. We could have created a single measure, but it would have been much more complex to understand and maintain.

TotalInvoicePaid DAX measure

TotalInvoicedPaid sums the AmountInvoiced fields but only for invoices that have been paid.

It’s DAX formula is:

TotalInvoicePaid =
   CALCULATE(
      SUM(Invoices[AmountInvoiced]), 
      (Invoices[Status] = "PAID")
   )

As you can see, it uses the SUM function on the AmountInvoiced field. It also filters the Invoices table to only include invoices whose Status is equal to PAID.

TotalInvoiceAmount DAX measure

TotalInvoiceAmount sums the AmountInvoiced for invoices that have actually been approved.

It’s DAX formula is

TotalInvoiceAmount = 
   CALCULATE(
      SUM(Invoices[AmountInvoiced]), 
      (Invoices[Status] = "AUTHORISED") || (Invoices[Status] = "PAID") || (Invoices[Status] = "SUBMITTED")
   )

This formula is almost identical in nature to TotalInvoicePaid. The only difference is that it includes invoices whose status are either AUTHORISED, PAID or SUBMITTED.

PercentPaid DAX measure

The final DAX formula to create the PercentPaid is as follows.

PercentPaid = Invoices[TotalInvoicedPaid] / Invoices[TotalInvoicedAmount]

This DAX formula simply divides TotalInvoicePaid by TotalInvoiceAmount. Notice that it did not need to use the CALCULATE DAX function. This is because it is built entirely from measures.

Understanding the basic syntax of DAX

The syntax of a measure

The basic syntax of a DAX measure is as follows.

MeasureName = Value

The name of the measure appears to the left of the equal ( = ) symbol. In the example above, the measures are named TotalInvoicePaid, TotalInvoiceAmount and PercentPaid.

The method used to calculate the value appears to the right of the equal ( = ) symbol.

The syntax to identify tables

Every endpoint you download into Power BI or Excel will become a Table for the purpose of DAX. Each table will include one or more field.

There are two ways to specify tables. 

TableName
'TableName'
  • You can use the first syntax if the table name only contains letters and numbers.
  • You can use the second syntax at all times.

For ease of entering DAX formulas, we strongly recommend you only use letters. Doing so ensures you do not need to enter apostrophes in your formulas.

In the example above, Invoices is the name of our table.

The syntax to identify fields

To identify a field or DAX measure, you must include the table name followed by the field or measure name within square brackets ( [ and ] ).

The basic syntax is:

TableName[FieldName]

In the examples above, we referenced both the Invoices[AmountInvoiced] and Invoices[Status] fields. We also referenced the Invoices[TotalInvoicedPaid] and Invoices[TotalInvoicedAmount] measures.

Note that we could have referenced the fields using the following syntaxes as well: ‘Invoices'[AmountInvoiced], ‘Invoices'[Status], ‘Invoices'[TotalInvoicedPaid] and ‘Invoices'[TotalInvoicedAmount].

The syntax of the DAX CALCULATE function

The syntax of the CALCULATE function is as follows

CALCULATE(Expression, filter1, filter2, filter3, ...)

The first parameter determines the operation to perform on a field. In our examples above, they perform the SUM function on the Invoices[AmountInvoiced] field.

The subsequent parameters allow you to apply filters. In our examples above, we filtered the Invoices[Status] field and looked for either the words AUTHORISED, PAID or SUBMITTED.

Special consideration of the DAX language

There are a number of special consideration regarding the DAX language that are worth being aware of.

DAX is not case sensitive.

Overall, DAX is not case sensitive. In other words, it does not matter if you use capital letters or not. The following refer to the same field of the same table.

Invoices[Status]
invoices[status]

This case-insensitiveness also applies to text strings. The following filters would produce the same results.

Invoices[Status] = "PAID"
invoices[status] = "paid"

DAX measures must have unique names across all tables

A DAX measure must have a unique name. This name must be unique across all tables.

In other words, there can only be a single TotalInvoicePaid measure across your whole Power BI or Excel workbook.

DAX measures are independent of the table on which they are defined

DAX measures provide an algorithm that is complete in and of itself. Because of this, they are independent of the table on which they are defined.

While you do declare a DAX measure within a specific table, in reality, this is just for ease of managing them.

For instance, you can define the TotalInvoicedPaid measure in any table and it will produce the exact same results.

While this might seem odd, it makes perfect sense if we look at the syntax. TotalInvoicePaid always sums the Invoices[AmountInvoiced] field and always filters the Invoices[Status] = “PAID”.

As a general rule, we recommend always defining the measures where they make the most sense.

DAX measures are dependent on the context in which the are used

It’s imperative to understand this and will stress it again. The only thing that changes the values the measure calculates is its context.

The context is determined by the other fields you include on your charts or visual element.