Column specified in the “SUMMARIZE” function cannot be found in the input table.

[matdub40]

I have a Power BI desktop file, I have an order table connected in multiple to 1 two-way to the customer table and a discount table connected in multiple to multiple two-way:

I display a table with information on customers who have received a discount on their orders.

This works fine, but now I'd like to display customers with orders without a discount in the same table. To do this, I right-click on one of the fields in the discount table and select “display items without data”, but it displays this error message:

“OLE DB or ODBC error: Query (59, 9) The column “<oii>CUSTOMER_CODE</oii>” specified in the “SUMMARIZE” function cannot be found in the input table.”

What causes this? I don't use this function.

Thanks for your help,

[FarhanJeelani · Accepted solution]

Hi @matdub40 ,

The issue you’re encountering with the error message in Power BI often happens because Power BI's “display items with no data” feature has some underlying limitations with certain types of relationships, particularly when using a two-way, many-to-many relationship.

Here’s why this issue likely occurs and some steps to resolve it:

Possible Cause
When you enable “display items without data” on a table with a two-way, many-to-many relationship, Power BI attempts to handle empty values in a way that can lead to unexpected queries. This can trigger an error in the background that involves `SUMMARIZE` or other functions even if you aren’t explicitly using them.

In this case, the error may be due to:

- The complexity of the many-to-many relationship and two-way filter direction between `Order`, `Customer`, and `Discount` tables.

- A mismatch between the column references that Power BI internally generates and the relationships or data fields in your model.

Steps to Resolve
Try the following methods to handle this requirement more effectively:

1. Use a Calculated Column or Measure:
- Create a calculated column in the `Order` table to flag orders with or without a discount. For instance:
DAX

HasDiscount = IF(NOT(ISBLANK(RELATED(Discount[DiscountID])), 1, 0)

- Then, in your table, you can filter on this calculated column to show both customers with and without discounts.

2. Switch to One-Way Filtering:
- If feasible, consider changing the relationship from two-way to one-way between `Order` and `Discount`. While two-way filtering is often convenient, it can introduce complexities and performance issues in scenarios like this.

3. Use an Outer Join in a DAX Measure:
- Create a measure that performs a left outer join on `Order` to `Discount`. For example:
DAX

CustomersWithDiscounts =
CALCULATE(
COUNTROWS(Order),
KEEPFILTERS(Discount[DiscountID]),
ALL(Discount[DiscountID])
)

- Use this measure in your table to determine which customers have orders without discounts.

4. Consider Using a Bridge Table:
- If the many-to-many relationship between `Order` and `Discount` tables is essential, create a bridge table to manage the connection indirectly, which might help Power BI better handle the "display items without data" feature without causing internal query issues.

 Testing Changes
After making adjustments, test your table view with “display items without data” enabled to see if it now works without errors.

Please mark this as solution if it helps . Appreciate Kudos😊