- Filter context is contextual data that can be filtered by. This can be a column of values in a data model (filter context) or a category in a visual (visual filter context)
- Row context creation (iteration) means that Power BI will no longer calculate the total or max of a single column but will now calculate its expression per row. It then overlays an overarching base expression, such as SUM, on top of this outcome. By calculating per row (iteration) instead of applying a calculation to one column, filter context is included. Even where no filter context was present in the first place.
- Filter context creation means that Power BI opens the possibility to modify and/or filter already existing filter context (such as a category in a visual).
The X stands for iteration expression. Iteration means that Power BI will no longer calculate the total or max of a single column but will now calculate its expression on a row-by-row basis. It then overlays an overarching base expression, such as SUM, on top of this outcome. By calculating per row (iteration) instead of applying a calculation to one column, filter context is included. Even where no filter context was present in the first place. This is further explained in the following example.
Let’s say our base expression is a MAX. Initially, one column of values is looked at and the MAX is calculated. Filter context is present as visual filter context in the visual itself through the customer category. In our example, we want to know the MAX per customer, but the total level must be a sum of the number of customers. By using the customer category in our visual, we created visual filter context. Each MAX now equals one customer. The filter context is missing on the total line. After all, the customer is not specified in the total line and therefore the total line sees the MAX of the entire column (in our case customer D). To overcome this, an iteration expression can be used. In the iteration expression SUMX, Power BI will look at the value of the indicated expression per row (in our case the MAX). Finally, it uses this created context for the base expression SUM.
With iteration expressions, one can influence the total rule. By looking at each row instead of the total, filter context is included. Even where no filter context was present in the first place. It should be noted, however, that this performance can be technically difficult. With an iteration expression like SUMX, Power BI will look at the values per line and then add them up. In big facts tables, this can cost a lot of performance. To primarily overcome this, an iteration expression should always be used in combination with the VALUES expression. The VALUES expression returns a single column of unique values. This drastically reduces the iteration and will improve the performance of the SUMX expression.
