3 Useful Beginner Level DAX Functions

DAX is a short form of Data Analysis Expressions, a formula language that enables developers to create custom calculations in the measures or calculate fields. DAX ideally will return one or multiple values as results.

DAX functions are pivotal in the usage of DAX during data modeling and for generating reports. The data models created in Microsoft Power BI and the Excel Power Pivot leverage the power of DAX. As a result, anyone with familiarity with Excel functions can easily resonate with the DAX Functions from a function name standpoint. However, the data types are modified to work with database tables and columns in a transactional database and hence making it possible to aggregate data dynamically.

Let us now look at three commonly used DAX Functions, which any Power BI developer will find it useful in their journey to create insightful dashboards and reports.

FILTER function – 1

The FILTER function returns a subset of a table or an expression, as indicated below. 

FILTER(<table>,<filter>)

For example, you have a table that stores the order data, and you would like to pull out data for items that are over a certain price, let’s say $750. To get this desired output, we will need to combine the COUNTROWS and FILTER functions.

Count of sales orders over 750 = COUNTROWS(FILTER(‘Sales’, ‘Sales'[Sales] > 750))

In the above example, the Sales parameter indicates the table. In contrast, the ‘Sales’ [Sales] > 750 parameter denotes a Boolean value (true/false), which is looked up against individual rows in a table to identify if it is greater than 750.

A key thing to note is that the DAX function FILTER is combined and used with other functions. You can notice that we used the FILTER to count the results from a subset of data.

ALL function – 2

As the name suggests, the ALL function will return all of the rows, either in a table or column, by ignoring any other filters applied.

ALL(<table> or <column>)

Assuming that you have a sales report with multiple cards and a page level filter to exclude a certain value associated with geo or region. Now, if you need to show the total quantity of products sold, a new card can be added with the ALL function to achieve this. Similar to the FILTER function, the ALL function is also used in conjunction with other functions. The syntax is shown below:

Count of all sales orders = COUNTROWS(ALL(‘Sales’))

CALCULATE in DAX – 3

CALCULATE is a popular function that is widely used to evaluate an expression in a modified filter context.

CALCULATE(<expression>, <filter1>,<filter2>…)

Taking the same Sales report example, if you would like to tabulate all the sales registered in all geo’s or regions, the CALCULATE function can help achieve this. 

Sum of sales all countries = CALCULATE(SUM(‘Sales'[Sales]),ALL(‘SalesGeography’))

In the above example, the SUM(‘Sales’ [Sales]) parameter indicates the column which has to be aggregated while the second parameter, ALL(‘SalesGeography’), denotes a Boolean to remove any applied filters that could have been applied on the SalesGeography table, like a region or geo to be excluded as quoted in the example for ALL function.

CALCULATE is a compelling DAX function. It is essential to note that you cannot have filter parameters reference measures and the expressions that cannot include functions that output or scan a table.

Though we have looked at a few essential DAX functions, we can create complex queries using DAX. We will explore more DAX functions in the upcoming blogs.

You can learn more about Calculate function in dax

For more useful blogs, please visit Learn DAX