How it works...

The reason why relating R09_ItemDistinct to R09_Items works, while relating R09_ItemValues to R09_Items does not work, comes down to a subtle but important difference between the DISTINCT function and the VALUES function. If you inspect the R09_ItemValues table, you will notice that there is a row with a blank value. This blank value comes from the relationship between the R09_Items table and the R09_Table table. Because there is a row in the R09_Table table (Item = Four) that does not match any row value in R09_Items, when VALUES evaluates the unique values in the Item column of R09_Items, VALUES adds this blank value. This means that the R09_ItemValues table created by the VALUES function is dependent upon the relationship between the R09_Items and R09_Table tables. Hence, when attempting to create a relationship between R09_ItemValues and R09_Items, a circular dependency is created between R09_ItemValues and R09_Table. This is likely highly unexpected.

Conversely, when using DISTINCT, only the actual values within R09_Items are considered, and not any relationships. Therefore, no circular dependency is present when creating the relationship between the R09_ItemsDistinct and R09_Items tables. Thus, if creating calculated tables within a data model, it is safest to use DISTINCT rather than VALUES.

The same subtle difference between VALUES and DISTINCT also exists between ALL and ALLNOBLANKROW.