Filter based on related filtered data

[SanderTK]

I'm stuck on what I believe is to be a relatively easy subject but for some reason I can't seem to find the right way to get to my desired result:

I've got a fact table of ticket sales and a dimension table of productions connected by a productionID. 

What I'm looking to do is to set a filter on a specific production and get a list of the top x productions that have been booked by the individuals that have booked a ticket for the filtered production.

What this means is that I am filtering a table of BookerIDs by production and then subsequently need to get a list of productions that are related to those filtered BookerIDs and get a count of BookerIDs per production that is filtered by the filtered table of BookerIDs. 

I also realize that it might be neccesary to use a bidirectional filter in order to filter back from the bookerIDs to the productions.

I'm not sure if I would have to use the booker dimension table seeing as though I do a count of the bookerIDs (from the fact.transaction table).

This would answer the business case of: What are the top 10 productions that were visited by the people that bought a ticket for production Y.

TABLES:

Dim.production:

productionID	Production
1	Titanic
2	Jungle Book
3	The Godfather
4	Batman
5	Superman
6	Spiderman
7	Aladin

Dim.booker:

bookerID	BookerName
1	Jack
2	Stacy
3	Robert
4	Anne
5	Ivette
6	Peter
7	John
8	Dirk
9	Gloria
10	Helen

Fact.ticketsales:

PersonID	ProductionID
9	5
10	7
6	2
8	5
9	4
6	5
8	3

DESIRED RESULT:

Filter: Superman

Count of PersonID	Production
1	Batman
1	The Godfather
1	Jungle Book

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a disconnected production slicer table like the attached sample file.

Please check the below picture and the attached pbix file.

Count of person measure: =
VAR list_bookerIDs =
    CALCULATETABLE (
        VALUES ( Ticketsales[bookerID] ),
        FILTER (
            ALL ( Ticketsales ),
            Ticketsales[productionID] = SELECTEDVALUE ( 'Production Slicer'[productionID] )
        )
    )
VAR list_lookerIDs_product =
    FILTER (
        Ticketsales,
        Ticketsales[bookerID]
            IN list_bookerIDs
                && Ticketsales[productionID] <> SELECTEDVALUE ( 'Production Slicer'[productionID] )
    )
RETURN
    COUNTROWS ( SUMMARIZE ( list_lookerIDs_product, Ticketsales[bookerID] ) )

[SanderTK]

That is a beautiful solution that I wouldn't ever have thought of.
I will apply it to my datamodel tomorrow and when I've got it working, I'll mark your answer as the solution.
Thank you so much!