I need help on setting up a relationship between 2 tables

[EaglesTony]

I have 2 tables:

Table1:

NAME

ABC

ABC

DEF

DEF

DEF

HIJ

Table2

ITEM         ITEMPREFIX

WAA-123   WAA

WAA-456   WAA

STE-1       STE

I have 2 slicers, the first uses Table1, the second uses Table2 and I want to filter Table2 based on the selected item in Table1.

Example:

If I select ABC from the first slicer, I want to show WAA-123 and WAA-456. (anything that has the ItemPrefix of WAA)

If I select DEF from the first slicer, I want to show WAA-123 and WAA-456.  (anything that has the ItemPrefix of WAA)

If I select HIJ from the first slicer, I want to show STE-1.  (anything that has the ItemPrefix of STE)

Any ideas, without duplicating data in Table2 ?

[PaulDBrown · Accepted solution]

This seems to work. Create a measure to use as a filter in the filter pane for table 2 along the lines of:

Filter Table 2 =
IF (
    ISFILTERED ( 'Table 1'[NAME] ),
    SWITCH (
        SELECTEDVALUE ( 'Table 1'[NAME] ),
        "HIJ", COUNTROWS ( INTERSECT ( VALUES ( 'Table 2'[ITEMPREFIX] ), { "STE" } ) ),
        COUNTROWS ( INTERSECT ( VALUES ( 'Table 2'[ITEMPREFIX] ), { "WAA" } ) )
    ),
    1
)

Add the measure to the filter pane and set the output value to = 1