One of the great new features in the September 2015 release of the Power BI Desktop is calculated tables.  I recently used it to create a table of pharmaceutical product groups, because the group names where present in the Product table but not in a column with unique values; therefore, I could not use the column on the one-side of a 1-many relationship.  I created the table Product Group with this formula:

Product Group =
DISTINCT(VALUES('Product'[Group]))


I then tried to create a relationship between the resulting column, which I renamed “Group”, and the column “Brand” on the Speaker Event table:

But this is what I got:

“Missing intermediate data”?  After a while, I discovered that at the very end of the Group column there was a blank value.  Removing it resolved the error.  I did this by changing the calculated table’s formula to this:

Product Group = FILTER(DISTINCT(VALUES('Products'[Group])),'Products'[Group] <> BLANK())

Please follow and like us: