Blog

Data Analysis Expressions, known as DAX, is the Function language that is used to solve data analysis problems in Power BI. DAX is used in the much the same way that you use Formulas to solve analysis problems in Excel. These expressions, when used in Measures, analyse our data by applying or removing specific filters from the data, filters that would be difficult to accomplish with the normal filtering tools in Power BI such as Slicers and Visual Level Filters.

DAX, like Excel comprises many functions designed for specific jobs. However, there are four functions that stand out above all the others as being the most useful in creating DAX calculations. These are CALCULATE, FILTER, ALL and RELATED.

CALCULATE

The CALCULATE function is used to apply your own filters to data, that can add to existing filters or even replace them. For example, it could be used when you need to draw together and organise sales for a particularly geographic region.

Example

To see the sum of sales for USA, you would use the calculate formula in a Measure like this:

=CALCULATE( SUM( ‘Sales Table’[Sales]),’Regions Table”[Region]=”USA”)

FILTER

Unlike CALCULATE, the FILTER function can’t change or modify filters; it can only further filter data within the current filters, producing even further subsets of the original data. FILTER is more commonly used inside the CALCULATE function when a simple filter expression cannot be used.

Example

For example, if you want to include another function in the filter expression e.g. to filter rows in your sales table that were greater than the average sales, you would need to use FILTER in a Measure like this:-

=CALCULATE( SUM(‘Sales Table’[Sales]),
FILTER( ‘Sales Table’, ’Sales Table’[Sales] >= AVERAGE( ‘Sales Table’[Sales]))

ALL

The ALL function removes filters from all the columns of a table if a table is references inside the brackets, or it will remove the filter from a specific column if a column is referenced. Just like the FILTER function, it is often nested inside CALCULATE.

Example

If you want to calculate percentages of a grand total, you can use the ALL function to return the grand total, so it can be used as the denominator, as in this example of a Measure:-
=SUM(‘Sales Table’[Sales]) /
CALCULATE( SUM( ‘Sales Table’[Sales]),ALL( ‘Sales Table’))

RELATED

Where two tables are related, the RELATED function can be used to pull values from the one side of the relationship into the table that sits on the many. This function is more often used in a Calculated Column or in a Measure that uses the “X” functions e.g. SUMX, AVERAGEX etc.

Example

You’ve related the Sales Table to the Products Table in a many to one relationship. You want to find the Sales value by multiply Quantity by Price, but the Quantity is in the Sales Table and the Price is in the Products table.

To do this, you could either create a Calculated Column in the Sales Table to pull the Price through like this:

=RELATED( ‘Products’[Price])

You could then multiply the new calculated column holding the price by the Quantity in the Sales table.

Or the other way (and many people would say a much better way) is to do this same calculation as a Measure using SUMX like this:-

=SUMX( ‘Sales Table’, ‘Sales Table’[Quantity] * RELATED (‘Products’ [Price])

Contact Burningsuit

Within Microsoft Power BI, it is possible to create some extremely sophisticated DAX queries. With just a little knowledge of how to create functions, you will be able to unlock your data and get a lot of very useful insights.

Burningsuit offers a whole range of high-quality end-user training courses covering all aspects of Microsoft Power BI and Excel. To find out more about our training courses, visit our Power BI training page and find out when our next courses are running. Want more information? Contact our team today on 0800 0199 746.