Tutorials › Power BI › FILTER, ALL and ALLEXCEPT

FILTER, ALL and ALLEXCEPT

Control and remove filter context with FILTER, ALL, ALLEXCEPT and ALLSELECTED

These three functions are used inside CALCULATE to precisely control which filters are active when a measure is evaluated. Mastering them means you can build any conditional metric — % of total, comparison to a fixed benchmark, metrics that ignore specific slicers. FILTER() — creates a table of rows that satisfy a condition ALL() — returns a table with all filters removed ALLEXCEPT() — removes all filters except specified columns ALLSELECTED() — returns values currently visible (respects outer filters but ignores inner)

Examples

FILTER — row-level filtering inside measures
FILTER returns a filtered table — used inside CALCULATE:

High Value Customers =
  CALCULATE(
    DISTINCTCOUNT(Sales[CustomerID]),
    FILTER(Sales, Sales[Amount] > 50000)
  )
→ Counts unique customers who had ANY order > ₹50,000

Orders from Delhi, Electronics =
  CALCULATE(
    COUNTROWS(Sales),
    FILTER(
      Sales,
      Sales[Region] = "Delhi" &&
      Sales[Category] = "Electronics"
    )
  )

WHEN TO USE FILTER vs direct CALCULATE filter:
  Simple: CALCULATE(SUM(...), Sales[Region] = "Delhi")
  Complex (row-level logic):
    CALCULATE(SUM(...), FILTER(Sales, Sales[Profit] / Sales[Revenue] > 0.2))
ALL, ALLEXCEPT, ALLSELECTED
ALL — removes ALL filters from a table or column:
  Total (Ignores All Filters) =
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))

  Total for Region Group (ignores Product filter only) =
    CALCULATE(SUM(Sales[Amount]), ALL(Sales[Product]))

ALLEXCEPT — remove filters EXCEPT specified columns:
  YTD Total (keeps Year, removes everything else) =
    CALCULATE(
      SUM(Sales[Amount]),
      ALLEXCEPT(Date, Date[Year])
    )

ALLSELECTED — respects what user has selected in slicers,
              ignores page/report-level filters:
  % of Slicer Selection =
    DIVIDE(
      SUM(Sales[Amount]),
      CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales[Region]))
    )
  → If slicer shows Delhi + Noida only,
    denominator = Delhi + Noida total (not grand total)

Key Points

  • ✓ALL() is the most common way to calculate "% of total" in Power BI
  • ✓FILTER() takes a table and a condition — returns only matching rows
  • ✓ALLEXCEPT() is useful for "keep this dimension's filter, remove all others"
  • ✓ALLSELECTED() calculates against what is currently visible to the user
  • ✓These functions are always used inside CALCULATE — never on their own in a measure

Practice Question

You want to calculate "Sales as % of Grand Total" that ignores all slicers. Which formula is correct?