Understanding Common DAX Errors: A Simple Guide

Scenario 01

What is the purpose of KEEPFILTERS in the following?

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

Suppose you have a sales table with the following data:

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:

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."

The error happens because DAX does not allow you to directly compare columns from two different tables inside a CALCULATE function when using logical conditions 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:

Store Table:

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:

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

Why This Causes an Error:

The issue arises because DAX does not allow a direct comparison of columns 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.

In case, Customer[CountryRegion] and Store[CountryRegion] are from different tables (Customer and Store). DAX cannot evaluate this condition across tables directly.

The Core Problem:

DAX requires that the filter condition involves a single table. 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:

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:

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

Here, CROSSJOIN creates a virtual table 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 used in a True/False expression that is used as a table filter expression. This is not allowed"

  

  The error occurs because of the use CALCULATE of a logical expression within another CALCULATE. 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:

    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:

    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:

      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:

      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:

• If you see a CALCULATE inside a logical expression (like >, <, =, etc.) in another, it likely causes this error.

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

    Top Price Threshold :=
    CALCULATE ( MAX ( Sales[Net Price] ) * 0.9, REMOVEFILTERS() )

Step 2: Use This Threshold in Your Measure

    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"

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:

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:

    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:

    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:

    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:

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;