Function Problems

Fix Power Pivot Lookup Returning Blank for Maximum Customer Value in Excel

John WilsonJohn Wilson Oct 9, 2026 868 views

Question details

The user needs to retrieve the year and quarter associated with the maximum expiration amount for each customer using a Power Pivot calculated column, but the DAX lookup formula is returning blank results.

How to Fix Power Pivot Lookup Returning Blank for Maximum Customer Value
Product
Excel 365 Pro Plus (Power Pivot)
Device & OS
not provided
Scenario
Creating a calculated column in a Power Pivot Data Model to identify the specific time period (Year/Quarter) of a customer's largest transaction, where customers have multiple records.
Observed behavior
The lookup formula fails to match the maximum value context properly, resulting in blank cells instead of the expected year and quarter data.
Before you start

Ensure that your data types are correctly configured in the Data Model (e.g., amounts set to Decimal/Currency and dates properly formatted) and that there are active relationships between your tables if multiple tables are used.

Solution 1Recommended

Use CALCULATE and FILTER to Isolate the Maximum Value

The most reliable method to retrieve the corresponding year and quarter is to calculate the maximum expiration amount per customer and filter the row context to match that specific amount.

Blank results in Power Pivot calculated columns usually occur when the filter context does not properly isolate the matching row. By using the CALCULATE function combined with FILTER and ALLEXCEPT, you can ensure the DAX formula evaluates the maximum value correctly for each unique Customer ID without losing the row context.

1
Open the Data Model

Navigate to the Power Pivot tab on the Excel ribbon and click 'Manage' to open the Power Pivot window.

2
Calculate the Maximum Amount

Select the target table. Click on the first cell of a new calculated column and enter a DAX formula to find the max value per customer: =CALCULATE(MAX([Amount]), ALLEXCEPT(TableName, TableName[CustomerID]))

3
Retrieve the Corresponding Year and Quarter

In another calculated column, use this formula to retrieve the matching date: =CALCULATE(VALUES(TableName[YearQuarter]), FILTER(TableName, TableName[Amount] = [MaxAmount] && TableName[CustomerID] = EARLIER(TableName[CustomerID])))

4
Verify the Results

Press Enter to apply the formula. Check the column to ensure it now populates with the correct year and quarter instead of blank values.

Use CALCULATE and FILTER to Isolate the Maximum Value
Handling Duplicate Maximum Values: If a customer has two records with the exact same maximum amount but in different quarters, the VALUES function will return an error. You must use FIRSTNONBLANK or TOPN instead to ensure only a single value is returned.
Free Microsoft Office alternative

Try WPS Office for Seamless and Efficient Data Analysis

If you frequently encounter complex formula errors or performance issues with heavy Excel add-ins like Power Pivot, WPS Office provides a lightweight, highly compatible alternative for everyday data analysis. WPS Spreadsheet supports advanced PivotTables and complex functions, handling large datasets smoothly without the heavy resource demands.

  1. 1. Download and Install: Download WPS Office from the official website and follow the simple installation instructions.
  2. 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file directly without needing to convert it.
  3. 3. Analyze Data Smoothly: Use the built-in Insert > PivotTable feature to quickly summarize your customer expiration data without complex DAX coding.
Fully compatible with Microsoft Excel (.xlsx, .xls) formatsAdvanced PivotTable functionality for easy data summarizationLightweight application that opens large datasets quickly and without laggingFree to use with a familiar, easy-to-navigate user interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power Pivot calculated column return blanks?

Blank results usually occur due to broken table relationships, mismatched data types (such as comparing a text column to a number column), or incorrect DAX filter contexts that fail to isolate a matching row.

How do I handle multiple records with the same maximum value in Power Pivot?

When using DAX to return a corresponding value, duplicate maximums can cause calculation errors. You can handle this by using functions like FIRSTNONBLANK, or by adding a secondary sort criterion (like the most recent date) within a TOPN function to ensure only a single value is returned.

Can I use Power Query to find the maximum value instead of Power Pivot DAX?

Yes. In Power Query, you can Group By the Customer ID to find the Max Expiration Amount, then merge this grouped table back with the original source data to extract the corresponding Year and Quarter before loading the final, clean table into the Data Model.