In addition to options we use on daily basis Data model has some advanced options too. With them, we can define the perspective view on the table. Also, we can determine the behavior of columns when they are dragged in the Pivot Table, as well as to set additional attributes table that can be used when creating reports. Finally, it is possible to make one or more synonyms for each column, which later makes it easier to search the model.
DAX possesses lots of information functions that return the value TRUE if the tested condition is fulfilled or FALSE if not. These functions can be useful when we want to test whether is certain value within table directly or indirectly filtered. Depending later, we can apply different formulas, and with the help of slicers can dictate what will be the result of the expression shown.
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…