Fix Power Pivot Lookup Returning Blank for Maximum Customer Value in Excel
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.

- 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.
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.
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.
Navigate to the Power Pivot tab on the Excel ribbon and click 'Manage' to open the Power Pivot window.
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]))
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])))
Press Enter to apply the formula. Check the column to ensure it now populates with the correct year and quarter instead of blank values.

Pre-calculate the Maximum Value using Power Query
If DAX formulas continue to return blanks or errors, use Power Query to group and merge the maximum values before loading the data into Power Pivot.
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. Download and Install: Download WPS Office from the official website and follow the simple installation instructions.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file directly without needing to convert it.
- 3. Analyze Data Smoothly: Use the built-in Insert > PivotTable feature to quickly summarize your customer expiration data without complex DAX coding.

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.




