Bringing a value from another table without using related

[kizofilax]

Hello, here is what I have

Case	pk
212	1
1212	2
444	1
111	3

<tbody>
</tbody>

Table B

pk	category
1	muffin
2	bagel
3	pizza
4	toast

<tbody>
</tbody>

I created a relationship between them and i know i can go to table A and do related(TableB'category'), the problem is that this creates another table and what I want to do is create a pivot table from A and show this:

case	category
212	muffin
1212	bagel
444	muffin
111	pizza

<tbody>
</tbody>

I need to show this table in this format as a pivot table (people want to see the category for each case)

If I just do this with that relationship what I get is something that looks like this:

111
	bagel
	muffin
	pizza
	toast

<tbody>
</tbody>

Its as if the relationship wasn't being recognized, any idea how to do this?

Thanks

 

[Mohammad Basem]

Hi kizofilax,

As per your sample data, I think you can have the same output using tables. This approach is simpler to construct, smart and expandable, and you don’t have to worry about extending the formulae or expanding the tables.

One simple VLOOKUP() formula is required to bring the category from table B into table A for each pk value.

 

[Matt Allington]

The issue is that the first table (I'll call it Table A) is on the many side of the relationship and Table B is on the one side. Filtering only ever flows from the one side to the many side. So when you put CASE from Table A in your pivot table rows, it will not filter Table B. When you then add CATEGORY from Table B to rows in your pivot, it is completely unfiltered and hence all values are visible in your Pivot. You could fix this by creating a measure that DOES filter TABLE B. Something like this

=CALCULATE(COUNTROWS(TableA),TableB)

Here is a sample workbook https://dl.dropboxusercontent.com/u/30711565/cross filter.xlsx