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 ALLSELECTED(TableName | ColumnName1[, ColumnName2[, ColumnName3[,…]]]] )
Or
ALLSELECTED()

The function has the following parameters:

• TableName: The name of an existing table, using standard DAX syntax. This parameter cannot be an expression.
• ColumnName: The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression.

Note: • If there is one argument, the argument is either tableName or columnName. If there is more than one argument, they must be columns from the same table.
• The Name of a table or column that we want to remove filter from is required unless being used as a CALCULATE modifier.

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.

Difference Between ALL, ALLSELECTED, and ALLEXCEPT Function