Daily DAX : Day 260 ALLEXCEPT

 The **ALLEXCEPT** DAX function in Power BI is used to modify the filter context in a calculation, removing filters from all columns in a table except for those explicitly specified. This allows you to perform calculations while retaining specific filters, which is useful for creating dynamic and flexible reports.

### Syntax

```dax

ALLEXCEPT(<table>, <column1>[, <column2>, ...])

```

- **table**: The table from which filters are removed.

- **column1, column2, ...**: The columns where existing filters are preserved.

### How It Works

- **ALLEXCEPT** removes filters from all columns in the specified table except for the columns listed in the function.

- It modifies the filter context for calculations, allowing you to aggregate data across a broader dataset while keeping certain filters intact.

- It’s commonly used in scenarios where you need to calculate totals or percentages relative to a specific subset of data, ignoring other filters.

### Use Case

**Scenario**: You have a sales dataset with columns like `Region`, `Product`, `Category`, and `SalesAmount`. You want to calculate the percentage of sales for each product within its region, ignoring any filters applied to `Category`.

#### Example

Suppose you have a table `Sales`:

| Region | Product | Category | SalesAmount |

|--------|---------|----------|-------------|

| East   | A       | X        | 100         |

| East   | B       | Y        | 150         |

| West   | A       | X        | 200         |

| West   | B       | Y        | 250         |

You want to calculate the percentage of sales for each product within its region, even if a filter is applied on `Category`.

#### DAX Formula

```dax

SalesPercentage =

DIVIDE(

    SUM(Sales[SalesAmount]),

    CALCULATE(

        SUM(Sales[SalesAmount]),

        ALLEXCEPT(Sales, Sales[Region])

    ),

    0

)

```

#### Explanation

- **Numerator**: `SUM(Sales[SalesAmount])` calculates the sales for the current filter context (e.g., a specific product and region).

- **Denominator**: `CALCULATE(SUM(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[Region]))` calculates the total sales for the region, ignoring any filters on `Category` or `Product` but preserving the filter on `Region`.

- **DIVIDE**: Computes the percentage by dividing the product’s sales by the region’s total sales.

#### Result

If a filter is applied on `Category = X`, the measure will still calculate the percentage based on the total sales in the region (including both categories X and Y). For example:

- For Product A in East: `100 / (100 + 150) = 40%`

- For Product B in East: `150 / (100 + 150) = 60%`

### Key Use Cases

1. **Percentage of Total**: Calculate metrics like market share or contribution to total within a specific group (e.g., region, year) while ignoring other filters.

2. **Dynamic Aggregations**: Perform calculations that need to respect only certain filters (e.g., time periods or regions) while ignoring others (e.g., product or category).

3. **Comparative Analysis**: Compare values within a group (e.g., sales within a region) without being affected by slicers or filters on other columns.

### Notes

- **ALLEXCEPT** is often used with `CALCULATE` to modify the filter context.

- Be cautious with large datasets, as removing filters can impact performance.

- If you need to remove all filters entirely, consider using `ALL` instead of `ALLEXCEPT`.

This function is powerful for creating flexible, context-aware calculations in Power BI reports.