Filtered table to filter another table

[BDolphin]

I am looking to filter a table based on the filtered results from a drill through. On the main page I have a table of names, I would like to drillthrough and go to their "Associates" on another page. On the Associates page i would have a list of the Occurrence numbers related to the person I drilled through on. I would like to use that list to filter a duplicate copy of the main table (so the drillthrough does not apply), and return all the names of people that are not the Name i used to drill through. 

Here a sample dataset

OccurrenceNo	Name
12345	Jim Bob
12345	Diane Richardson
12345	Sally Ann
12345	Joe Smith
12346	Jim Bob
12346	Joe Smith
12346	Jack Howard
12346	Diane Richardson
12346	Thomas Taylor
12347	Jim Bob
12347	Michael Peterson
12348	Jim Bob
12348	Sally Ann
12349	Jim Bob
12349	Diane Richardson
12350	Diane Richardson
12350	Joe Smith

If I drill through on Jim Bob, these are his occurrences, I need to use to get the names of his associates

These are the results I want

Thanks

[v-alq-msft]

Hi, @BDolphin 

Based on your description, I created data to reproduce your scenario.

Table:

Name Table:

You may create two measures as follows.

Isdiplay = 
IF(
    SELECTEDVALUE('Table'[Name]) = MAX('Name Table'[Names]),
    1,0
)

Count = 
SUMX(
    'Name Table',
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Name] = MAX('Name Table'[Names])
            )
        )
    )
)

Then you can put 'Isdisplay' to the visual level filter and set the field for drill through as 'Name'.

Finally when you drill through from the Page1, you will get the expected result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BDolphin]

Thanks Allan, 

Its almost right, I only want the names of the people that share an occurrence with Jim Bob, for example, so the results should be 

On the last occurrence 12350 - Diane and Joe Smith are the only names listed so they should not be counted when drilling through on Jim Bob if that makes sense. 

Bre 

[v-alq-msft · Accepted solution]

Hello, @BDolphin

You can modify the "Count" measure as follows.

Count = 
SUMX(
    'Name Table',
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Name] = MAX('Name Table'[Names])
                &&  'Table'[OccurrenceNo] in DISTINCT('Table'[OccurrenceNo])
            )
        )
    )
)

result:

Best regards

Allan

If this post helps,then please consider Accepting it as the solution to help the other members find it more quickly.

[BDolphin]

@v-alq-msft 

I have one more request, I am trying to put a card that would show the Count of the Names, so based on the example it would show the number 6.  

Thanks,  

[BDolphin]

Thank  you so much, that worked perfectly