Niluthpal Chowdhury
Power BI
ALL
ALLSELECTED
ALLEXCEPT
Power BI Filter Functions
Niluthpal Chowdhury
What is ALL function
All Function is a filter function in PowerBi that
returns all the rows in a table, or all the values
in a column, ignoring any filters that might
have been applied. This function is useful for
clearing filters and creating calculations on all
the rows in a table.
All function can take Table, Column or columns
as an argument.
Syntax
ALL( [<table> | <column>[,
<column>[, <column>
[,…]]]] )
Niluthpal Chowdhury
Understand Visually
Here I have Customer_Job column from
Customer table and their corosponding
Total Income
Niluthpal Chowdhury
Let’s apply ALL function
Here I have applied
ALL function
passing Table as an
argument
We Can see that ALL measure is giving the result
576M for every row and it is the sum of “Total
Income” which means it has igonored the filter
context applied on the visual and giving us the
entier result as a result in each row.
Niluthpal Chowdhury
Let’s apply column as an argument.
Here I have passed
customer_job
column as an
argument
We Can see that nothing changed that is
because we have used Customer_job as filter
over this visual
Niluthpal Chowdhury
Here I have passed
Education_level
column as an
argument
When passed differnt column name as an
argument we can see that measure values have
changed it is showing values which is equal to
the Total Income which means it is now filtering
on the basis of the filter context applied in the
visual which is Customer_job.
Niluthpal Chowdhury
Let’s Understand ALLSELECTED
function
ALLSELECTED function removes context filters
from columns and rows in the current query,
while retaining all other context filters or
explicit filters.
ALLSELECTED funtion takes column as an
argument.
Syntax
ALLSELECTED([<tableName> |
<columnName>[,
<columnName>[,
<columnName>[,…]]]] )
Niluthpal Chowdhury
Let’s apply ALLSELECTED
function
Here I applied
ALLSELECTED
function passing
Customer_Job
column as an
argument
As we can see ALLSELECTED function is again
giving the samne result which is Sum of “Total
Income” for every row.
So how does it differnet from ALL function ?
Niluthpal Chowdhury
Slicer
In this scenario, a slicer for customer_job has been
added, and the ALLSELECTED measure dynamically
responds to the slicer selections. This shows how
ALLSELECTED respects explicit filters applied
through slicers, retaining the selected context while
removing direct filters from the current calculation.
Niluthpal Chowdhury
Let’s Understand ALLEXCEPT
function
All EXCEPT function removes all context filters
in the table except filters that have been
applied to the specified columns. This function
is particularly useful when you want to clear
most of the filters applied to a table but retain
the filters on certain key columns.
Syntax
ALLEXCEPT(<table>,<column>[,
<column>[,…]])
Let’s apply ALLEXCEPT function
Here I applied
ALLEXCEPT
function passing
Customer_Job
column as an
argument
As we can see that the ALLEXCEPT function is
ignoring filters from all other columns except for the
specified one. In this visual, it disregards filters from
the education level column and only applies the
filters from the customer_job column, ensuring the
calculation respects the selected customer_job filter.
Niluthpal Chowdhury
FOLLOW ME
FOR SUCH
CONTENT
Like, Share and do
Comment your
Thoughts
