Getting started with Data Analysis Expressions (DAX) can be intimidating, but becoming knowledgeable in just a few basic functions can help you to unlock many new insights into your data. While it is easy to create visuals in Power BI or Pivot Charts, we often desire to view data for specific time frames, or with specific filters applied or removed, that we are unable to accomplish with our raw data.
If you’ve ever run into this problem, then having some basic knowledge of DAX can enable you to create new views of the data in your model. As a relative newcomer to Power BI and DAX, I find myself using the 5 following DAX functions most often. Let’s work through some common business scenarios with the following Power BI file, available here (note: must have Power BI to open):
Report View
| Data View | ||
| Date Table | Sales Table | SalesGeography Table |
 |
 |
 |
Relationships View
1. FILTER
The FILTER function is used to return a subset of a table or expression, as shown below.
FILTER(
,)Let’s say that you want to get a count of items sold at the premium level, which you define as anything over $100. We will use the COUNTROWS function, which counts the number of rows in the specified table, along with the FILTER function to accomplish this:Count of sales orders over 100 = COUNTROWS(FILTER(‘Sales’, ‘Sales'[Sales] > 100))The first parameter, ‘Sales’, identifies a table or an expression that results in a table. The second parameter, ‘Sales'[Sales] > 100, represents a Boolean, or true/false expression that is evaluated for each row of the table. In this expression, we are passing the Sales table to the FILTER function and asking it to return any sales that are over $100. The FILTER function is never used as a standalone function, but is used in conjunction with other functions. In the example above, we use the FILTER function to return a subset and then count the results.2. ALL The ALL function is used to return all of the rows in a table, or values in a column, ignoring any filters that may have been applied.ALL(or )In the Report View above, we have a report with multiple cards and a page-level filter that excludes sales in Germany. We would like to keep this filter, but add a card visual that shows the total number of items sold, ignoring any filters placed on the rest of the report. The following expression that incorporates the ALL function can help you to achieve this: Count of all sales orders = COUNTROWS(ALL(‘Sales’))In this example, we pass the ‘Sales’ table to the ALL function, asking it to clear any filters that may have been placed on it. Like the FILTER function, the ALL function is not used standalone but in conjunction with other functions. In this case, we use the ALL function in conjunction with the COUNTROWS function to get a count of all sales records. The ALL function accepts either a table or a column and clears any filters that may have been placed on them.3. RELATED The RELATED function returns a related value from another table (example shown below).RELATED()So far, we’ve worked with functions that can help you to return a subset or clear any filters on a table or column. We would now like to filter our sales for only the United States, but don’t have all of the data we need in one table to accomplish this. Fortunately, we have the RELATED function, which we can use 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 the SalesGeography table, respectively, we can use the following expression that incorporates the RELATED function to return a count of sales orders for only the United States:Count of sales orders in the US = COUNTROWS(FILTER(ALL(‘Sales’), RELATED(‘SalesGeography'[Countries]) = “United States”))4. TOTALYTD/ TOTALQTD/ TOTALMTDTime intelligence functions in DAX enable you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.TOTALYTD(,[,][,])Continuing from the examples above, let’s say that you would like to see the total sales to date for this year. The following expression that incorporates the TOTALYTD function can enable you to easily do this: Total sales this year = TOTALYTD(SUM(‘Sales'[Sales]), ‘Dates'[Dates])The first parameter, ‘Sales'[Sales], identifies the column that you would like to aggregate. This could also be an expression that returns a scalar, or singular value. The second parameter, ‘Date'[Dates], is a column that contains dates. Time intelligence functions are immensely useful functions that eliminate the need for complex code in calculating aggregations over commonly used periods of time.5. CALCULATE The CALCULATE function evaluates an expression in a context that is modified by specific filters.CALCULATE(, ,…)Let’s say you are now interested in tabulating all sales for all areas. While you could create some piecemeal expressions to accomplish this, you can easily and cleanly accomplish the same thing utilizing the CALCULATE function. The following example, which uses the CALCULATE function, can accomplish this:Sum of sales all countries = CALCULATE(SUM(‘Sales'[Sales]),ALL(‘SalesGeography’))The first parameter, SUM(‘Sales'[Sales]), identifies the column that you would like to aggregate. The second parameter, ALL(‘SalesGeography’), represents a Boolean that removes any filters that may have been placed on the SalesGeography table. Notice that this ignores the page-level filter that excludes sales in Germany. The CALCULATE function is one of the most powerful and useful functions in DAX. It is helpful to think of the CALCULATE function as a supercharged “IF” statement. A couple of rules apply to the CALCULATE function: The filter parameters cannot reference measures, and expressions cannot use any functions that scan or return a table. The CALCULATE function is typically used with aggregation functions, and although the filter parameters are optional, at least one is typically used.It’s possible to create some very sophisticated queries in DAX, but being well versed in just a few functions can help you to unlock many interesting insights into your data. I would recommend checking out Microsoft’s DAX reference for more information. If you still have questions or just want to chat about Power BI, contact us and we will be glad to help!
