This is a common Power BI (DAX) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem A Power BI report contains the following tables: FactSales DimDate DimProduct DimRegion Business users want to build several KPI cards: Total Company Sales (ignoring all report filters) Total Sales for the selected Year while ignoring Product filters Percentage of Sales based on the user's current slicer selections The developer needs to decide whether to use ALL() , ALLEXCEPT() or ALLSELECTED() . As the Power BI developer, answer the following questions: What does ALL() do? What does ALLEXCEPT() do? What does ALLSELECTED() do? When would you use each function? How do these functions affect the filter context? Which function is most suitable for calculating percentages in interactive reports? Why?

Answer & Explanation

**1. What ALL() does**
ALL removes every filter that has been applied to the column or table you specify. When you call ALL( DimProduct ) the filter context on DimProduct is cleared, so the calculation sees all products regardless of any slicer or visual filter. If you use ALL() without an argument it removes filters from the entire row‑context of the current table. This is why ALL is the go‑to function for “grand‑total” or “ignore all filters” calculations.

**2. What ALLEXCEPT() does**
ALLEXCEPT also clears filters, but it keeps the filters on the columns you list. The syntax is ALLEXCEPT( Table, Column1, Column2,… ). All filters on the table are removed except those that involve the listed columns. For example, ALLEXCEPT( FactSales, DimDate[Year] ) will ignore product, region, month, etc., but will still respect the Year filter that the user has selected.

**3. What ALLSELECTED() does**
ALLSELECTED removes the filters that come from the visual’s internal row context (for example the rows that make up a table or matrix) while preserving the filters that the user has chosen through slicers, page filters, or report‑level filters. In other words, it returns the set of rows that are currently selected by the user, not the full table, but it ignores the automatic filtering that the visual imposes on its own rows.

**4. When to use each function**

- **ALL** – Use when you need a value that is completely independent of any filter, such as “Total Company Sales” that must ignore every slicer, page filter, or visual filter. It is also useful for calculating a denominator in a percent‑of‑total where the denominator must be the overall total.

- **ALLEXCEPT** – Use when you want to keep one or more specific filters while discarding the rest. A typical scenario is “Total Sales for the selected Year while ignoring Product filters.” You would keep the Year filter (DimDate[Year]) and remove everything else, so the measure still reacts to the year slicer but not to product slicers.

- **ALLSELECTED** – Use when the calculation should respect the user’s explicit selections (slicers, report filters) but should not be affected by the visual’s own row context. This is ideal for interactive percentages, rankings, or running totals that need to reflect what the user has chosen, for example “Percentage of Sales based on the user’s current slicer selections.”

**5. How the functions affect filter context**

- **ALL** wipes out the filter context for the specified columns/tables. After the function is evaluated, the measure sees an empty filter on those columns, so any existing filters are ignored.

- **ALLEXCEPT** first removes all filters on the table, then reapplies the filters that exist on the columns you list. The resulting filter context contains only those retained columns.

- **ALLSELECTED** keeps the filter context that originates from slicers, page filters, and report filters, but removes the filters that come from the visual’s own row context. The measure therefore works on the set of rows that the user has actively selected.

**6. Which function is most suitable for calculating percentages in interactive reports and why**
ALLSELECTED is generally the best choice for percentage‑of‑total calculations in interactive reports. The reason is that a percentage usually needs to be based on the total that the user is actually looking at (the total of the rows they have selected with slicers). ALLSELECTED gives you that “selected total” while ignoring the visual’s internal filtering, so the numerator (e.g., sales for a specific product) and the denominator (total sales for the current selection) are evaluated over the same user‑defined set. Using ALL would give you the absolute company total, which would make the percentage static and unresponsive to slicers. Using ALLEXCEPT would keep only the columns you specify, which may unintentionally drop other slicer selections the user expects to be considered. Therefore, for dynamic, user‑driven percentages, ALLSELECTED provides the correct balance of respecting user choices and removing visual‑level filters.