Introduction to DAX Functions and Measures

Nov 30, 2023 | Power BI Hints and Tips

DAX functions are like functions in Excel.  There is an overlap with some functions available in both products but there are many functions that are available in one but not the other.

DAX functions can be used to create a calculated column or a measure.  Measures are a more efficient way to calculate in Power BI.

Creating a Calculated Column

  1. From Power BI Desktop go to the Data View.
  2. On the Table tools tab select New Column.
New column icon image

3. The formula bar will read ‘Column =’.  Change Column to a suitable name and put the calculation after the ‘=’.

4. Calculations in Power BI use field names as shown below.  The field is in square brackets and the table before that.  When you create the calculation a list of field names will appear that you can choose from.
e.g. Sales Amount = ‘Order Details'[Quantity]*’Order Details'[Unit Price]

Creating a Measure

Power BI measures are the way of defining calculations in a DAX model, which helps us to calculate values based on each row.  The result of a measure doesn’t show in the data but can be seen when the measure is added to a visualisation.

  1. From Power BI Desktop go to the Data View.
  2. On the Table tools tab select New Measure.
New measure icon image

3. The formula bar will read ‘Measure =’.  Change Measure to a suitable name and put the calculation after the ‘=’ and is similar to the calculated column above in structure.

Storing Measures together

If you are working with lots of measures it helps to store all of them together.

  1. From the Home tab select Enter Data.
Enter data icon image

2. Do not enter any data into the table.  Name the table Key Measures.  (This can be called something else but NOT measures).

3. Click on Load.

4. Select the key measures table.

Always click on this table before creating a measure.  When the first measure is created the table will still contain an empty column called column 1.  Once this has been deleted the symbol for the whole table changes to represent a measure and is moved to the top of the list of tables so it is easily accessible in future.

Key measures table image

Examples of useful DAX Functions

There are hundreds of DAX functions that can be used in Power BI.  Here are just a handful to get you started.

SUM

Adds all the numbers in a column.

=SUM(column name)

SUMX

Returns the sum of an expression evaluated for each row in a table.

=SUMX(Table, expression)

CALCULATE

Evaluates an expression in a modified filter context.  It can also be used to stop the data being filtered based on the context of the data.

For example, if you already have a measure that works out the total amount and it is put into a table visualisation along with a product field then the calculate function could create a measure that is the overall total amount that would be the same for each product.  These 2 columns could be divided to produce a percentage of the overall figure.

=CALCULATE(expression, [filter1])

DIVIDE

Performs division and returns alternate result or BLANK() on division by 0.  It prevent the issue of divide by zero errors.

=DIVIDE(numerator, denominator, [Alternate result]

COUNTROWS

The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.

=COUNTROWS([Table])

RELATED

The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table.  Without this function each measure will only work with data from the same table.

=RELATED(Column name)

SAMEPERIODLASTYEAR, SAMEPERIODLASTMONTH, SAMEPERIODLASTQUARTER

These 3 functions work in a similar way to each other but are either related to year, month or quarterly data.  Using SAMEPERIODLASTYEAR as the example: Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

=SAMEPERIODLASTYEAR(Dates)

DATEADD

This is more flexible than the SAMEPERIOD functions mentioned above as the number of periods is flexible and the shift in data can be forward or backwards.  It returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

=DATEADD(Dates, Number of Intervals, Interval)

Further Reading

If you’ve enjoyed reading this introduction to DAX Functions and Measures, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Power BI? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Power BI Desktop Courses I run.