"Lookup Table" stops working when filtered on field in unrelated table

[#1]

Here's the Load Script

RealData:

LOAD Line, Entity,  Value INLINE [

    Line, Entity, Year, Value

    1, Nate, 2010, 1.1

    1, Navya, 2010, 1.11

];

LineLookup:

LOAD Line, Category INLINE [

  Line, Category

  1,  First Category

  2,  First Category

  3,  Second Category

];

And here's the result.

I'm expecting the table not to ignore the relationship between "Category" and "Line" where I've marked in red.

• Line 2 should be in First Category
• Line 3 should be in Second Category

(Ideally Category remains as the first column [does that mean it must be a Dimension?] , so that the "grouping effect" of the "Pivot Table " > "Always Fully Expanded" will continue to group by Category)

[swuehl · Accepted solution]

Maybe like attached.

You probably know that selecting in field Entity does filter the records, so in your selected set, there is no dimension 2. You would need to use set analysis to overcome this (check my added expression, hidden).