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.

In this example we will use analysis of traffic volumes in the company that is engaged in wholesale of alcoholic and non-alcoholic beverages. In this regard, you have to first create a measure:

SumaK:=SUM(Transakcije[KOL])

If you want to see this amount with all filters disengaged (all columns on the basis of which it is possible to make filters) in the table Artikli (Items) will write the following expression:

SumaALLT:=SUMX(ALL(Artikli);[SumaK])

If we want to remove filters explicitly for column KLASIFIKACIJA (Classification) we would write this expression:

SumaALLC:=SUMX(ALL(Artikli[KLASIFIKACIJA]);[SumaK])

Sometimes it is necessary to remove all filters except for one column. If it’s column KLASIFIKACIJA, to create such filter we should write expression:

SumaALLEXCEPT:=SUMX(ALLEXCEPT(Artikli;Artikli[KLASIFIKACIJA]);Transakcije[SumaK])

At the end, sometimes we want to emphasize use of filter over one column. In that case we’re using function ALLSELECTED:

SumaALLSELECTED:=SUMX(ALLSELECTED(Artikli[KLASIFIKACIJA]);Transakcije[SumaK])

If we add all these measures in Power Pivot report, then drag dimension PROIZVOĐAČ (MANUFACTURER) in Rows and add slicer KLASIFIKACIJA (CLASSIFICATION) we would get this report:

As you can see, if we use the function of ALL in which the argument is a table or function ALLEXCEPT, explicitly we annihilate filters which results in that all the values in the table contain the final sum. This can be useful when you later want to compare measures and calculate various percentages.