# Understanding Common DAX Errors: A Simple Guide

### Scenario 01

What is the purpose of **KEEPFILTERS** in the following?

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724217602143/f51cde7f-3ad3-488d-9b41-b2295d26b40f.png align="center")

Let’s explore the concept `KEEPFILTERS` with an example to clarify how it works.

Suppose you have a sales table with the following data:

| Product | Quantity | Net Price | Country | Brand |
| --- | --- | --- | --- | --- |
| A | 10 | 50 | Canada | Contoso |
| B | 5 | 300 | United States | Contoso |
| C | 20 | 25 | Canada | Contoso |
| D | 15 | 70 | United States | Fabrikam |
| E | 3 | 500 | Canada | Contoso |

You want to calculate the total sales amount, but only for transactions where:

* The quantity multiplied by the net price is greater than 1000.
    
* The country is either "Canada" or "United States."
    
* The brand is "Contoso."
    

The DAX expression provided is:

```sql
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( Sales[Quantity] * Sales[Net Price] > 1000 ),
    Customer[Country] IN { "Canada", "United States" },
    'Product'[Brand] = "Contoso"
)
```

### How Does `KEEPFILTERS` Affect the Calculation?

#### Without `KEEPFILTERS`:

Let’s say there’s already a filter applied to the data model that limits the sales to products with a net price greater than 100.

When the expression runs:

* The condition `(Sales[Quantity] * Sales[Net Price] > 1000)` would **replace** any existing filters on the `Sales` table.
    
* The original filter on `Net Price > 100` would be overridden by this new filter, and the only condition applied would be `Sales[Quantity] * Sales[Net Price] > 1000`.
    

This could lead to unexpected results because the initial filter context would be lost.

#### With `KEEPFILTERS`:

By using `KEEPFILTERS`, the original filter on `Net Price > 100` remains active while also applying the additional filter `(Sales[Quantity] * Sales[Net Price] > 1000)`.

This layered approach results in a more restrictive filter. Only rows that meet both the original filter and the new condition will be included.

### Practical Example:

Using the data above, let’s break it down:

1. **Base Filter** (assuming it exists in the context):
    
    * `Net Price > 100`
        
    * Affects products B, D, and E.
        
2. **Without** `KEEPFILTERS`:
    
    * The new filter `(Sales[Quantity] * Sales[Net Price] > 1000)` is applied.
        
    * This overrides the base filter and selects only product E (where `3 * 500 = 1500`).
        
    * The existing filter on `Net Price > 100` is ignored.
        
3. **With** `KEEPFILTERS`:
    
    * The existing filter `Net Price > 100` remains.
        
    * The additional filter `(Sales[Quantity] * Sales[Net Price] > 1000)` is applied.
        
    * Both filters together result in selecting only product E, because it meets **both** conditions.
        

In this case, the results are similar, but in more complex scenarios with multiple layers of filters, using `KEEPFILTERS` ensures that you’re combining, not replacing, filter conditions.

### Key Takeaway:

* **Without** `KEEPFILTERS`: The new filter can override existing filters on the same columns.
    
* **With** `KEEPFILTERS`: The new filter is layered on top of existing filters, ensuring all conditions are respected.
    

This approach gives you more precise control over the filter context in complex DAX calculations.

---

### **Scenario 02**

*"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."*

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724225549241/8fb4d0b2-b7f0-42ac-80b2-1287627eebcf.png align="center")

The error happens because DAX does not allow you to directly compare columns from <mark>two different tables</mark> inside a `CALCULATE` function when using <mark>logical conditions </mark> like `=` or `>`. The error message specifically states: **“The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.”**

Let’s break down why this happens using an example.

Suppose you have two tables:

#### Customer Table:

| CustomerID | CountryRegion |
| --- | --- |
| 1 | United States |
| 2 | Canada |
| 3 | United States |

#### Store Table:

| StoreID | CountryRegion |
| --- | --- |
| 1 | United States |
| 2 | Canada |
| 3 | Mexico |

### Goal:

Let's assume we want to calculate the total sales amount for customers who are in the same country as the store they are purchasing from.

DAX expression is:

```sql
Same Country Sales :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( Customer[CountryRegion] = Store[CountryRegion] )
)                                
                                 ❌
```

### Why This Causes an Error:

The issue arises because DAX <mark>does not allow a direct comparison of columns</mark> from different tables in the filter condition when using `CALCULATE`. This is because DAX expects a logical expression within `KEEPFILTERS` to work on a **single table**.

<mark>In case, </mark> `Customer[CountryRegion]` <mark>and </mark> `Store[CountryRegion]` <mark>are from </mark> **<mark>different tables</mark>** <mark>(Customer and Store). DAX cannot evaluate this condition across tables directly.</mark>

### The Core Problem:

DAX requires that the filter condition involves a <mark>single table</mark>. The comparison of columns from multiple tables within the same logical expression is not allowed in this context.

### Example to Clarify:

Imagine your original code without the `KEEPFILTERS` function:

```sql
daxCopy code=
CALCULATE (
    [Sales Amount],
    Customer[CountryRegion] = Store[CountryRegion]
)
                               ❌
```

This code will also produce the same error because the condition compares columns across tables.

### How to Solve This:

To fix this issue, you need to first **relate the tables** using a relationship or a virtual table that combines the data from both tables.

#### Using a Virtual Table:

You can create a virtual table that joins the Customer and Store tables and then apply your filter:

```sql
Same Country Sales :=
CALCULATE (
    [Sales Amount],
    FILTER(
        CROSSJOIN(Customer, Store),
        Customer[CountryRegion] = Store[CountryRegion]
    )
)                                ✅
```

Here, `CROSSJOIN` creates a <mark>virtual table</mark> containing all possible combinations of rows from both tables, and then `FILTER` applies the condition to find matching countries.

### Key Takeaways:

* **DAX requires a single table** for logical comparisons in filter expressions within `CALCULATE`.
    
* When comparing columns from different tables, use `RELATED`, `LOOKUPVALUE`, or create virtual tables to bring both columns into the same context.
    
* The error occurs because DAX does not support directly comparing columns from different tables in a simple filter expression.
    
    ---
    
    ### Scenario 03
    
    *"A function ‘*[*CALCULATE‘ has been*](https://dax.guide/calculate/?aff=sqlbi) *used in a True/False expression that is used as a table filter expression. This is not allowed"*
    
    ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724226403239/d842c57d-edce-4734-8852-f3a2f02b96c0.png align="center")
    
    <mark>The error occurs because of the use </mark> `CALCULATE` <mark>of a logical expression within another </mark> `CALCULATE`<mark>.</mark> Specifically, the issue is that when the filter argument is a logical expression (e.g., `Sales[Net Price] > ...`), DAX does not allow the use of any function that triggers context transition inside that expression.
    
    ### Let’s Break Down the Problem
    
    Here’s the code causing the error:
    
    ```sql
    Sales Top Prices :=
    CALCULATE (
        [Sales Amount],
        Sales[Net Price] > CALCULATE ( MAX ( Sales[Net Price] ) * 0.9, REMOVEFILTERS() )
    )
                                      ❌
    ```
    
    The goal of this measure is to return the total sales amount for products where the net price is in the top 10% of all prices.
    
    #### Why Does This Code Fail?
    
    The issue is that you’re using a `CALCULATE` function inside a logical expression (`Sales[Net Price] > ...`) within the main `CALCULATE`. This is not allowed because `CALCULATE` performs context transition, which cannot be nested in this way when used as a filter predicate.
    
    ### What Is Context Transition?
    
    Context transition occurs when a row context (which usually applies within iterators like `SUMX`, `FILTER`, etc.) is converted into a filter context. This happens when you use `CALCULATE`, which is designed to transition from a row context to a filter context.
    
* However, in this case, the issue is that `CALCULATE` is used inside a logical expression that itself is within another `CALCULATE`. DAX cannot handle the context transition within this kind of nested logical filter expression.
    
    ### Detailed Example to Illustrate the Problem:
    
    #### Data Example:
    
    Suppose you have a `Sales` table with the following data:
    
    | `Product` | `Net Price` | `Quantity` | `Sales Amount` |
    | --- | --- | --- | --- |
    | `A` | `100` | `10` | `1000` |
    | `B` | `80` | `5` | `400` |
    | `C` | `120` | `8` | `960` |
    | `D` | `60` | `15` | `900` |
    
    You want to calculate the total sales amount for products where the net price is in the top 10% of all prices.
    
    You try this:
    
    ```sql
    Sales Top Prices :=
    CALCULATE (
        [Sales Amount],
        Sales[Net Price] > CALCULATE ( MAX ( Sales[Net Price] ) * 0.9, REMOVEFILTERS() )
    )
    ```
    
    1. The inner `CALCULATE` is used to determine the threshold for the top 10% of prices:
        
        ```sql
        CALCULATE ( MAX ( Sales[Net Price] ) * 0.9, REMOVEFILTERS() )
        ```
        
        This returns 90% of the highest price (which is 120, so the result is 108).
        
    2. The outer `CALCULATE` is supposed to filter rows where the `Sales[Net Price]` is greater than 108:
        
        ```sql
        Sales[Net Price] > 108
        ```
        
        But because the logical expression involves a `CALCULATE` (which causes context transition), DAX cannot handle this scenario.
        
    
    ### Finding Context Transition Issues:
    
    A general rule to identify potential context transition issues is:
    
    * <mark>If you see a </mark> `CALCULATE` <mark>inside a logical expression (like </mark> `>`<mark>, </mark> `<`<mark>, </mark> `=`<mark>, etc.) in another, it likely causes this error.</mark>
        
    
    ### How to Resolve This Issue?
    
    To avoid the problem, you need to break down the calculation into steps and avoid nesting context transitions in logical expressions. Here’s a possible solution:
    
    #### Step 1: Calculate the Threshold Separately
    
    ```sql
    Top Price Threshold :=
    CALCULATE ( MAX ( Sales[Net Price] ) * 0.9, REMOVEFILTERS() )
    ```
    
    #### Step 2: Use This Threshold in Your Measure
    
    ```sql
    Sales Top Prices :=
    CALCULATE (
        [Sales Amount],
        Sales[Net Price] > [Top Price Threshold]
    )
    ```
    
    This approach separates the calculation of the threshold from the filtering logic, avoiding the issue with nested context transitions.
    
    ### Conclusion:
    
    The error happens because you’re trying to nest `CALCULATE`, which performs context transition, inside a logical filter expression within another `CALCULATE`. The key to avoiding this issue is to split your calculation into multiple steps, ensuring that context transitions are not nested in logical expressions.
    
    * Context transition is always triggered by `CALCULATE`, but it’s especially problematic when nested inside logical expressions are used as filter conditions.
        
    * The error happens because DAX cannot handle a context transition inside a logical expression used for filtering.
        
    * The best practice is to break down the logic into smaller, separate steps to avoid this issue.
        
    
    ---
    
    ### Scenario 04
    
    *"A function ‘PLACEHOLDER’ has been used in a True/False expression that is used as a table filter expression. This is not allowed"*
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1724227041915/183520f8-a23a-4b54-9f0c-5c081bf77166.png align="center")

#### Why Storing the Measure in a Variable Doesn’t Always Work:

In some cases, simply storing the measure in a variable (like in the first example) might not solve the issue. This is because the real problem could be that the logical condition itself is too ambiguous. For example:

* Should the margin filter be applied at the customer level, the store level, or across all sales transactions?
    

### Clarifying the Requirement:

The key issue here is understanding the target of the filter:

* **Customer Level:** Should the filter apply only if a customer’s overall margin is greater than 60%?
    
* **Store Level:** Should the filter apply only if a store’s overall margin is greater than 60%?
    
* **Transaction Level:** Should the filter apply to each sales transaction where the margin is greater than 60%?
    

### Example to Clarify:

Suppose you want to filter sales where the margin at the **transaction level** is greater than 60%. You could rewrite the measure as:

```sql
Sales Profitable :=
CALCULATE (
    [Sales Amount],
    Sales[Profit] / Sales[Sales Amount] > 0.6
)
```

Here, the logical condition directly references columns from the `Sales` table, making it clear that the filter applies to individual transactions. This approach avoids the context transition issue because it doesn’t involve any ambiguous measure reference.

### Summary of the Issue:

1. **Measure References in Logical Expressions:** The core issue is using measures (which involve context transition) in logical expressions inside `CALCULATE`. DAX cannot handle context transition in these scenarios.
    
2. **PLACEHOLDER Error:** The error refers to a "PLACEHOLDER" because DAX replaces the measure reference with a `CALCULATE` function internally, causing the context transition.
    
3. **Fixing the Issue:** Either evaluate the measure first using a variable or clarify the filtering logic to avoid using ambiguous measure references in logical expressions.
    
4. **Clarifying Requirements:** Ensure that the filter target is clear (e.g., customer level, store level, transaction level) to write an effective DAX measure.
    

By understanding the context transition mechanism and the limitations of logical expressions within `CALCULATE`, you can avoid these common pitfalls in DAX.

### Understanding Filter Contexts in DAX

When we talk about "margin," it’s essential to understand **where** and **how** this margin is being evaluated:

* **Transaction Level:** The margin could be calculated for each sales transaction (e.g., one row in the `Sales` table).
    
* **Customer Level:** The margin could be aggregated for a customer across all their purchases.
    
* **Store Level:** The margin could be aggregated for an entire store across all its sales.
    

In DAX, when you use a measure like, the value of that measure depends on the current filter context. The ambiguity lies in **what context** you want `[Margin %] > 0.6` to apply to.

### Why Does This Matter?

Let’s break down what happens in different contexts.

1. **Transaction-Level Margin:** If you want to filter sales where the margin is greater than 60% for **each transaction**, you would write:
    
    ```sql
    CALCULATE(
        [Sales Amount],
        Sales[Profit] / Sales[Sales Amount] > 0.6
    )
    ```
    
    Here, you’re directly referencing the columns `Sales[Profit]` and `Sales[Sales Amount]` within the filter condition. This applies the filter to each row in the `Sales` table.
    
2. **Customer-Level Margin:** If you want to filter sales based on a customer's **overall margin** (e.g., across all transactions for that customer), you would need to aggregate the margin for each customer and then apply the filter:
    
    ```sql
    CALCULATE(
        [Sales Amount],
        CALCULATE(
            [Margin %],
            ALLEXCEPT(Customer, Customer[CustomerID])
        ) > 0.6
    )
    ```
    
    Here, the margin is calculated at the customer level, and the filter context is adjusted accordingly.
    
3. **Store-Level Margin:** Similarly, if the goal is to filter based on a store’s overall margin, you would adjust the filter context to the store level:
    
    ```sql
    CALCULATE(
        [Sales Amount],
        CALCULATE(
            [Margin %],
            ALLEXCEPT(Store, Store[StoreID])
        ) > 0.6
    )
    ```
    

### Why Does DAX Care About This Distinction?

DAX operates based on filter contexts, and the same measure (e.g., `[Margin %]`) can produce different results depending on the level of granularity. When you say `[Margin %] > 0.6`, DAX needs to know whether you mean:

* Margin calculated at the transaction level?
    
* Margin aggregated at the customer level?
    
* Margin aggregated at the store level?
    

This context determines how the filter is applied and what rows get included in the calculation.

### Why Is This Relevant to the Error?

In the original measure:

```sql
Sales Profitable :=
CALCULATE(
    [Sales Amount],
    [Margin %] > 0.6
)
```

DAX does not know **how** to apply `[Margin %] > 0.6` because `[Margin %]` is a measure, and it might involve context transition (depending on how it’s defined). If `[Margin %]` is calculated at a different level (e.g., customer or store), the filter becomes ambiguous.

That’s why the error suggests that the filter definition is incomplete—you need to be explicit about **what level** the filter should be applied at.

Tip: When the measure defined(\[Margin\]) is based on another measure this pattern could be identified.

### Summary:

* The explanation about customer-level, store-level, and transaction-level margins is not about redefining "what margin is" but about clarifying **how DAX calculates and applies filters** in different contexts.
    
* The ambiguity comes from DAX needing to know at what level `[Margin %] > 0.6` should be applied, which can vary depending on the scenario.
    
* In other calculations like, this ambiguity is usually less problematic because "sales amount" typically applies consistently across contexts.
    

In essence, DAX is very context-sensitive, and clarifying the context helps ensure that the measure works as intended, avoiding errors and unexpected results.

### **Sample file(live) from SQLBI to play with;**

<iframe width="800" height="486" src="https://app.powerbi.com/view?r=eyJrIjoiZWE2ZmVkZDgtOWI0Yi00OTJjLWE0ZmQtZDRjZTc1YmQzM2QzIiwidCI6ImViNWZhYzY2LTM2ZDctNDU3MS1iOGNhLWY3ZWU5NTc4ZDQ1YyIsImMiOjEwfQ%3D%3D"></iframe>