Forum Discussion

BDolphin's avatar
BDolphin
Helper I
6 years ago
Solved

Filtered table to filter another table

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

 
OccurrenceNoName
12345Jim Bob
12345Diane Richardson
12345Sally Ann
12345Joe Smith
12346Jim Bob
12346Joe Smith
12346Jack Howard
12346Diane Richardson
12346Thomas Taylor
12347Jim Bob
12347Michael Peterson
12348Jim Bob
12348Sally Ann
12349Jim Bob
12349Diane Richardson
12350Diane Richardson
12350Joe 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

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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:

    a1.png

    Best regards

    Allan

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

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      BDolphin
      Helper I
      6 years ago

      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's avatar
        v-alq-msft
        Community Support
        6 years ago

        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:

        a1.png

        Best regards

        Allan

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