Some Common Useful DAX Functions for Beginners

Welcome back guys. Just like i said in my previous post, getting started with Data Analysis Expressions (DAX) can be intimidating, but becoming knowledgeable will guide you through unlocking new insights into your data. I believe the following DAX functions can get you started on the right path. Let’s work through some common business scenarios.

1. FILTER:

The FILTER function is used to return a subset of a table or expression, as shown below.

Let’s say that you want to get a count of items sold based on specific amount range.e.g Amount between 150 and 250. We will use the COUNTROWS function (Just like the name, it counts record), which counts the number of rows in the specified table, along with the FILTER function to achieve this:
Count of sales between 150 and 250 = COUNTROWS(FILTER('Sales', 'Sales'[SalesAmount] >= 150 && <= 'Sales'[SalesAmount]))
In the example above, we are passing the Sales table to the FILTER function and asking it to return any sales that are between 150 and 250 and then count the results with COUNTROWS function.

2. ALL:

The ALL function is used to return all of the rows in a table, or all the values in a column, ignoring any context filters that may have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table or all the values in a column.

Let's say we have a result set showing sales by geographic location. e.g europe, asia, africa etc. and now we want to add another column showing count of all sales ignoring the geographic locations. Using the ALL function within the COUNTROWS function, you can achieve this:
Count of all sales = COUNTROWS(ALL('Sales'))
In this example, we pass the 'Sales' table to the ALL function, asking it to clear any context filters that may have been placed on it, before applying the COUNTROWS function and providing us the result.

3. RELATED:

The RELATED function returns a related value from another table. The function follows a many-to-one relationship, If a relationship does not exist, you must create a relationship.

So let's say we want to filter our sales by Spain, but don’t have all of the data we need in one table to accomplish it. Here is the interesting part. We can use the RELATED function to retrieve values from one table to another through an established relationship. Given that there is a many-to-one relationship between the Sales table and our SalesGeography table as i mentioned above, we can accomplish this:
Count of sales in Spain = COUNTROWS(FILTER(ALL('Sales'), RELATED('SalesGeography'[Countries]) = "Spain"))

4. TOTALYTD / TOTALQTD / TOTALMTD :

These calculates the 'year, quarter and month running total' or you can say 'year, quarter and month running sum'. The expressions evaluate year-to-date , quarter-to-date and month-to-date. They are Time intelligence functions. Time intelligence functions in DAX enable you to compare data expressions over different time periods.

So let’s say that we want to see the running total sales by year, quarter and month. Here we can use the TOTALYTD function to achieve this:
Year running total = TOTALYTD(SUM('Sales'[SalesAmount]),DateTime[DateKey])
Quarter running total = TOTALQTD(SUM('Sales'[SalesAmount]),DateTime[DateKey])
Month running total = TOTALMTD(SUM('Sales'[SalesAmount]),DateTime[DateKey])

5. CALCULATE:

The CALCULATE function evaluates an expression in a context that is modified by the specific filters.

Let’s say we want to get total sales in all locations. We can achieve the-same using CALCULATE function to archive this:
Sum of sales in all locations = CALCULATE(SUM('Sales'[SalesAmount]),ALL('SalesGeography'))
See how we implemented CALCULATE function and added our previous ALL function with a simple SUM function. Awesome right?. Cool.

6. ALLEXCEPT:

The ALLEXCEPT function removes all context filters in the table except filters that have been applied to the specified columns. (Try and understand the use of ALL function vs ALLEXCEPT function).

This calculation below sums SalesAmount and uses the ALLEXCEPT function to remove any context filters on the DateTime table except if the filter has been applied to the CalendarYear column:
Sum of sales = CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))
In the example above, we are passing the Sales table to the FILTER function and asking it to return any sales that are between 150 and 250 and then count the results with COUNTROWS function.

If you still have questions or just want to chat about DAX in Microsoft SQL Server Analysis Services, Power Pivot in Excel, and Power BI Desktop, contact me and i will be glad to help!