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....
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.

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.

      • BDolphin's avatar
        BDolphin
        Helper I
        6 years ago

        Thank  you so much, that worked perfectly