DAX Lookup with multiple results for visual

[Krcmajster]

Hi All, 

I have a table as below:

Cost Center ID     Contract ID         Value 1           Value 2

1                           1A                       A                     A

1                            1B                      b                      b

1                            1C                      c                       c

2                             2A                     A                      A

2                             2b                     B                       B

And I have a dashbord that shows individual Contract ID

What I need is to list all other contract IDs that are associated to the saame cost center

So when I drill down for Contract 1A  I want a table where I will have a table or matrix as below:

Other Contracts:

1B

1C

And same if I drill down for Contract 1B:

Other Contracts:

1A

1B

How can I lookupd with multiple results and also ignor the drill throught filter

[v-lili6-msft · Accepted solution]

hi, @Krcmajster 

After my research, you could try this way:

Step1:

Duplicate the basic table. (It could not be achieve in basic table, because if you select one data, other data will be filtered )

Step2:

Create a measure like this:

DAX Lookup = 
CALCULATE (
    MAX ( 'Duplicate Table'[Contract ID] ),
    FILTER (
        'Duplicate Table',
        AND (
            'Duplicate Table'[Cost Center ID] IN VALUES ( 'Table'[Cost Center ID] ),
            NOT ( 'Duplicate Table'[Contract ID] ) IN VALUES ( 'Table'[Contract ID] )
        )
    )
)

Step3:

Drag data fields from duplicate table into table visual and also drag this measure into visual level filter and set is not blank.

Result:

Best Regards,
Lin