Fix Blank RELATED Function Results in Excel Power Pivot
Question details
The user needs to determine why the DAX RELATED function returns a blank value when retrieving data from another table in a Power Pivot data model.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- Using a DAX formula to retrieve related column values and calculate a result across tables in the data model.
- Observed behavior
- RELATED returns an empty value instead of the expected related value.
Open the Power Pivot window and identify the table containing the formula, the lookup table, and the columns intended to connect them. Confirm that the model has been refreshed after any source-data or relationship changes.
Correct the RELATED formula and test each referenced column
Use this method when the formula may contain a misspelling, an incorrect table or column reference, or multiple RELATED expressions that need to be checked separately.
RELATED retrieves a value from the one-side lookup table through an existing relationship. It must be spelled correctly and reference columns that are reachable from the table where the calculated column is being evaluated.
In Excel, select Power Pivot > Manage, select the table containing the calculated column, and click the formula bar for the affected column.
Replace any misspelled function such as RELETED with RELATED. For example, enter =RELATED('Cookie type'[Cost Per Cookie])*RELATED(Order[Units Sold]) when both referenced columns are valid for the current row context.
Create or edit a temporary calculated column with =RELATED('Cookie type'[Cost Per Cookie]) and then test the other reference separately to identify which lookup returns blank.
In the Power Pivot field list, confirm that names such as 'Cookie type', Cost Per Cookie, Order, and Units Sold exactly match the names used in the formula.
Create or repair the relationship between the Power Pivot tables
Use this solution when the formula is correct but Power Pivot cannot locate a related row in the other table.
Check matching keys, data types, blank source values, and refresh the model
Use this solution when a relationship exists but only some rows return blanks.
Work with Excel-compatible spreadsheets in WPS Office
Power Pivot and DAX model issues are specific to Microsoft Excel. For everyday spreadsheet editing, calculations, reporting, and sharing, WPS Office provides a free, lightweight alternative with a familiar spreadsheet interface and seamless migration of compatible Excel files.
- 1. Download WPS Office: Select Download WPS Office Free and run the installer for your device.
- 2. Open your spreadsheet: Launch WPS Spreadsheets and select Open to choose an existing .xlsx, .xls, or .csv workbook.
- 3. Review worksheet formulas: Click a formula cell and use the formula bar to review or update standard spreadsheet formulas.
- 4. Save in an Excel-compatible format: Select Menu > Save As and choose Excel Workbook (*.xlsx) to share the file with Excel users.
Frequently Asked Questions
Why does RELATED return blank even though the formula has no error?
A blank result often means that the current row has no matching key in the related table, the related source value is blank, or the relationship does not connect the tables in the required direction.
What is the correct syntax for the Power Pivot RELATED function?
Use =RELATED(TableName[ColumnName]). For example, =RELATED('Cookie type'[Cost Per Cookie]) returns Cost Per Cookie from the related Cookie type table.
Can RELATED work without a Power Pivot relationship?
No. In Power Pivot, RELATED requires an existing relationship path between the table containing the formula and the table containing the requested column.
How do I check relationships in Excel Power Pivot?
Select Power Pivot > Manage, then select Home > Diagram View to inspect relationship lines. You can also use Design > Manage Relationships to review, edit, or create relationships.
Do the two relationship columns need the same data type?
Yes. Matching key columns should use compatible data types. Check each key in Power Pivot Data View and use Home > Data Type to align them before refreshing the model.
