Forum Discussion

SanderTK's avatar
SanderTK
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Filter based on related filtered data

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

 

 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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] ) )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      SanderTK
      Icon for Advocate II rankAdvocate II
      4 years ago

      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!