DAX functions with the prefix “ALL” are used to engage or disengage filters in Power Pivot reports. These may be implicit filters, we have created by dragging dimensions in Filters field, but more often it comes to interactive filters- slicers and timeline, we use to filter Pivot Tables. The recipe that follows is about a report with a comparative review of these functions for their better understanding.
DAX has a class of functions that perform data filtering. For given arguments, as a result, they return filtered table. Filter functions are usually used in combination with another DAX function, and their specificity is to allow creating measures that ignore the limitations of existing filters in a Pivot Table, as well as interactive filters such as Slicers and Timeline. In this recipe you will be briefly acquainted with these functions.
CALCULATE is one of the most commonly used functions in DAX. It is used to calculate expressions, usually given by some aggregation function, with use of one or more filters. It is not incorrect to use CALCULATE without a filter, but that’s not much point. The strength of this function is the ability of combining expression with a range of DAX functions that narrow set of data to perform aggregation with…
In addition to the usual functions for data aggregation DAX has a special class of functions that change context of the execution for computing operations, and yet we call them the “iterators”. Sometimes we also call them the “X” functions, because after the name of the aggregation operation (SUM, AVERAGE, COUNT) stands the letter “X”, which indicates that these are iterators (SUMX, AVERAGEX, COUNTX). More in the text that follows…
In one of the earlier recipes was discussed about Data model, tables that we’re adding to it and relationships between them. This relationships are not very useful when when we’re writing DAX expressions. If we wan to use, as a formula argument, column from related column you should somehow emphasize it. This is done by using functions RELATED and RELATEDTABLE, and about which you can read more in the following text …