Referential Integrity, PowerPivot Relationships, and the Unknown Member

This section discusses some advanced concepts related to missing values in PowerPivot tables that are connected by relationships. This section might be useful to you if you have workbooks with multiple tables and complex formulas and want help in understanding the results.

If you are new to relational data concepts, we recommend that you first read the introductory topic, Understanding Relationships.

Referential Integrity and PowerPivot Relationships

PowerPivot does not require that referential integrity be enforced between two tables in order to define a valid relationship. Instead, a blank row is created on the “one” end of each one-to-many relationship and is used to handle all non-matching rows from the related table. It effectively behaves as a SQL outer join.

In PivotTables, if you group data by the one side of the relationship, any unmatched data on the many side of the relationship is grouped together and will be included in totals with a blank row heading. The blank heading is roughly equivalent to the "unknown member."

Understanding the Unknown Member

The concept of the unknown member is probably familiar to you if you have worked with multidimensional database systems, such as SQL Server Analysis Services. If the term is new to you, the following example explains what the unknown member is and how it affects calculations.

Suppose you are creating a calculation that sums monthly sales for each store, but a column in the Sales table is missing a value for the store name. Given that the tables for Store and Sales are connected by the store name, what would you expect to happen in the formula? How should the PivotTable group or display the sales figures that are not related to an existing store?

This problem is a common one in data warehouses, where large tables of fact data must be logically related to dimension tables that contain information about stores, regions, and other attributes that are used for categorizing and calculating facts. To resolve the problem, any new facts that are unrelated to an existing entity are temporarily assigned to the unknown member. That is why unrelated facts will appear grouped in a PivotTable under a blank heading.

Treatment of Blank Values vs. the Blank Row

Blank values are different from the blank rows that are added to accommodate the unknown member. The blank value is a special value that is used to represent nulls, empty strings, and other missing values. For more information about the blank value, as well as other DAX data types, see Data Types Supported in PowerPivot Workbooks.

See Also

Other Resources

Creating and Working with Calculations

Key Concepts in DAX

Context in DAX Formulas