ALLSELECTED DAX Function in Power BI

The ALLSELECTED function in Power BI (DAX) is used to modify the filter context by returning all the values in a table or column as if the visual-level filters were removed but still respects the filters coming from outside the visual (filter from slicers or filters).

DAX Syntax 1) ALLSELECTED(Table) It removes the visual filter context from the specified table. The Table parameter specifies the name of an existing table, using standard DAX syntax. This parameter cannot be an expression. 2) ALLSELECTED(ColumnName1[, ColumnName2[, ColumnName3[,…]]]] ) It remove the visual filter context from the specified columns. The parameter ColumnName specifies the name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression. Please notes the columns must be from the same table. 3) ALLSELECTED() It removes all the visual filter context from the calculation.

Example: We have added a slicer with the Country field from Sheet1 table. Also, a table visual with the Country field and a measure “10th Marks Measure”.

The measure is calculated by the following DAX formula:

DAX

In the above DAX formula, we are summing the 10th Marks column of the Sheet1 table.

As we know the measure is filtered on the visual levels also, so it is filtered automatically based on Country field added in the table visual.

Now let’s our requirement is to filter the measure on the external filter like slicers on the page but not by the visual level filter, then we can modify the measure calculation as shown below:

DAX

The output of the above code is shown in the image below:

Here, the measure value 6229 is the sum of Brazil, Canada, India, and Switzerland, but as we can see in the image.

Let’s add the Name column to the table, we can see our measure is not filtered even on the basis of name also from table.

But for example, our requirement is to not filter the measure based on Country column, we want the measure to filter on the Name visual filter. To implement this, we can modify our measure formula, by specifying the column in the ALLSELECTED function from which we want to remove the filter.

DAX

To see its effect lets use the matrix instead of table.

We can see that the measure is not filtered based on country visual filter, but it is filtered on the basis of Name visual filter.

Please note that if the Name is repeated in the multiple countries, then that Name gives the sum from all the countries.

To demonstrate this, let’s use the following data:

Now let’s remove the visual filter from the Country column of the table Sheet20.

DAX

The output of the above dax function is shown below:

Specify Multiple Filters The ALLSELECTED function removes visual filters on Country column, but still the boolean filter reintroduces a filter condition that limits the calculation to Australia. All the filter expressions are ANDed to each other.

DAX

The output of the above dax function is shown below:

DAX

The output of the above dax function is shown below:

Difference Between ALL, ALLSELECTED, and ALLEXCEPT Function