One Solution to the “missing intermediate data” Error on Power BI Relationships

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 =

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

Try Create Relationship

But this is what I got:

Error Message

“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())



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>