Connecting multiple columns to a dimension and filtering

[tamara_nsb]

Hi,

I have 4 columns that reference the same value and need to connect to the same dimension table and get filtered by this.  Is this possible?   Any product can have upto 4 types, no forced order

Product	Type1	Type2	Type3	Type4
Bike	3	2	1	4
Doll	1	4
Gi Joe	2	4
crayons	1	2	4

TypeID	TypeName
1	Girls Toy
2	Boys Toy
3	Sports
4	5-10yrs

I need the visual table to look like this:

Product	Type1	Type2	Type3	Type4
Bike	Sports	Boys Toy	Girls Toy	5-10yrs
Doll	Girls Toy	5-10yrs
Gi Joe	Boys Toy	5-10yrs
crayons	Girls Toy	Boys Toy	5-10yrs

And I need to add a filter/Slicer,   so if Boys toy is selected it will show Rows 1,2,4.   If Sports is selected it will only show row 1.    Then 5-10 shows all.

How can I do this?  I know you can't have multiple active relationships between the table,  I can merge the Type table 4 times to get 4 new columns with the names,  but then how do I get the slicer to work?

[lbendlin · Accepted solution]

Not pretty but works. First you need to unpivot your data to make it usable, then you need to (gasp!) create a bidirectional link between fact and dimension.