Checking the filters’ application

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.

To check whether some column has filter applied we’ll use function:

ISFILTERED (<column>)

For example, if we want to find out if column PROIZVOĐAČ (MANUFACTURER) is filtered, in table Artikli we will write following expression:

Filtrirana:=ISFILTERED(Artikli[PROIZVOĐAČ])

Sometimes it’s important to find out whether column is cross filtered. In that case we’ll use function:

ISCROSSFILTERED (<column>)

Since one manufacturer often has several brands we’ll use the function to write expression:

PosrednoFiltrirana:=ISCROSSFILTERED(Artikli[ROBNA MARKA])

Sometimes we want to know if there is only one filter applied on column. In that case we use:

HASONEFILTER (<column>)

For example, if we have made a slicer using column PROIZVOĐAČ (MANUFACTURER) and we want to check if only one filter is applied, we’ll use expression:

JedinstvenFilter:=HASONEFILTER(Artikli[PROIZVOĐAČ])

At the end, sometimes we need unique (distinct) value of one column. For that purpose we’ll use function:

HASONEVALUE (<column>)

…so we can use it to write following expression:

JedinstvenaVrednost:=HASONEVALUE(Artikli[PROIZVOĐAČ])

Let’s make a Power Pivot report with the measures that we have just created (drag them in Values area). In the Rows area let’s add dimension KLASIFIKACIJA (CLASSIFICATION) and then two more slicers: PROIZVOĐAČ (MANUFACTURER) and MARKA (BRAND). If we filter table PROIZVOĐAČ by choosing filter value Nectar we’ll get following report:

Measure Filtrirana returns value TRUE, because we have chosen Nectar in slicer PROIZVOĐAČ. Measure PoserednoFiltrirana is also TRUE, because the column BRAND is indirectly filtered. This is because in a group of data we’re processing Nectar has only three brands. In the picture you can see that the other brands appear „pale“ colors.

Measure JedinstvenFilter has a value of TRUE because we just clicked on Nectar. If we had chosen both values Nectar and Fruvita it would return value FALSE. Measure JedinstvenaVrednost in this case always have a value of TRUE because the name of Nectar in the list of all manufacturers appears only once.