And why do I get a "The value for column 'item_pack' in table 'Table_on_Items' cannot be determined in the current context.", when I create a calculated column "=Query[bottles]/Table_on_items[item_pack]"? I created the relations, and that seemed to be a no-brainier. Table_on_item is a linked table and Query is a SQL query.
Even after the releationship is created, when I try to create a non-compound measure
I get: The column 'table_on_items[item_pack]' either doesn't exist or doesn't have a relationship to any table available in the current context.
Edit: First I had to create the column item_pack on my Query table, within PowerPivot, using Related. Then I was able to create a column named cases, where cases = bottles / item_pack. I would say that something should kick-in and tell you to use the related function when it looks like you are trying to do something obvious that doesn't work.
Everything else is surprisingly quick, even with 30 million rows.