Compressing pictures

“A picture speaks more than 1000 words” so you leave a better impression when you enrich the report with pictures. However, many users do not care how big they are and, accordingly, Excel files grow progressively. Even Office 2010 got an option for image optimization that you can use whether you use Word, Excel, PowerPoint … Optimization reduces images, which loses quality, but files take up less space. How to determine the right ratio?

Comparison of filters

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.

Filter functions

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.

Function CALCULATE

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…

Iterator functions

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…