Movie Dashboard Show "Actors worked with" selected Actor

[Governator]

Dear PowerBI champions,

I have been trying to produce a certain table in my PowerBI Movie Dashboard (I like watching movies and keeping track of them). 

In this dashboard I have all the basic information about movies I've watched and haven't watched yet, including actors, directors, IMDb ID etc. What I would like to visualize however is:

When you select a certain actor in a slicer, you get to see all the other actors he/she has worked with in a table, and how many times they have worked together (based on the number of movies or unique IMDb ID's they both appear in). I've tried many things like basket analyses and believe some extra table with the same data will have to be connected somehow, but haven't figured it out...

I have included a sample PBI file with just selected Jack Nicholson movies, so if you select Jack Nicholson in the slicer on the left you get to see his data. The table on the right should then show all the other actors worked with and how many movies together.

Here's the sample PBI dashboard: Sample Dashboard Movies PBI 

And the connected Excel file: Sample Dashboard Movies Excel file 

BONUS QUESTION: If I make a reference table of the Movies table in PBI because I want to keep the original table unedited (the source Excel table has all Actors, Genres and Directors piled up in one cell per movie), would that require different steps to create the requested visualization? Since table connections in PBI will then not work the same way...

Thanks for the help!

[garygraf]

Hi Everyone,

Great idea—adding an “Actors Worked With” view to the movie dashboard would really elevate the experience, letting you instantly see the network of collaborations tied to the selected actor. Since the dashboard already tracks watched and unwatched movies, directors, and IMDb IDs, this new layer would fit naturally into the flow and make the whole app feel more connected and insightful!@new

Bundle Of Thanks!

[asallycobb]

If you want to visualize "Actors Worked With" in your movie dashboard, where selecting an actor displays their frequent collaborators and the number of films they’ve worked on together, you’ll need to establish a many-to-many relationship in Power BI (or a similar tool). Start by creating a bridge table that maps every possible actor pair per movie. For example, if a movie has three actors (A, B, and C), the table should store combinations like A&B, A&C, and B&C. Next, use DAX or Power Query to count how many times each pair appears across different movies (based on IMDb IDs for accuracy). Finally, connect this table to your main dataset and use a matrix or table visualization to display the results dynamically when an actor is selected in a slicer.

[jefferyruff]

Dear PowerBI champions,

It’s fantastic that you’re building such an engaging Movie Dashboard! Tracking actors and their collaborations is not only fun but also an insightful way to explore your favorite movies.

To achieve what you’re aiming for – showing all the other actors an actor has worked with and the count of their collaborations – here’s a practical approach:

Steps to Create the Desired Table:

1. Create a Relationship Table

   • Create a new table in PowerBI that captures actor pairs for every movie. This table will have columns like MovieID, Actor1, and Actor2.
   • If your dataset already has the movie cast listed (e.g., in a comma-separated format), you can use Power Query to split the cast and generate all possible combinations of actors for each movie.

2. Filter Logic

   • When you select an actor in the slicer, you need a measure that filters the relationship table to only show rows where the selected actor is either Actor1 or Actor2.

3. Count Collaborations

   • Add a calculated measure to count the number of movies (or unique IMDb IDs) where both actors appear together. For example:
     DAX

     Copy code

     Count of Collaborations = COUNTROWS( FILTER( RelationshipTable, (RelationshipTable[Actor1] = SELECTEDVALUE(ActorSlicer[Actor])) || (RelationshipTable[Actor2] = SELECTEDVALUE(ActorSlicer[Actor])) ) )

4. Visualize the Data

   • Use a table visual to display the filtered actors along with the count of their collaborations. Ensure you exclude the selected actor from the results (e.g., use a filter like Actor != SELECTEDVALUE(ActorSlicer[Actor])).

Practical Example:

Let’s say you select Jack Nicholson from the slicer. Your table will dynamically list all the actors he has worked with (e.g., Morgan Freeman, Diane Keaton) and show the count of movies they appeared in together.

Bonus Tip: Enhance the Experience with Castle TV

To further elevate your dashboard experience, consider integrating a movie discovery platform like Castle TV. Castle TV provides a seamless way to explore movie metadata, actor filmographies, and collaborations, which could complement your dashboard perfectly. You can fetch detailed data about movies, actors, and connections that would make your dashboard richer and more insightful.

[MAwwad]

To create a table that shows all the other actors worked with and how many movies they have worked together with a selected actor, you can create a new table using DAX formulae and join it with the existing tables. Here are the steps you can follow:

1. Create a new table called "Actors Worked With" with columns "Actor A", "Actor B", and "Movies Worked Together".
2. In the "Actor A" column, use the selected actor from the slicer.
3. In the "Actor B" column, use the DISTINCT function to get a unique list of all the actors who have worked with the selected actor. You can use a formula like this:

Actor B = DISTINCT ( FILTER ( VALUES ( Movies[Actor] ), CALCULATE ( COUNTROWS ( Movies ), FILTER ( Movies, Movies[Actor] = EARLIER ( Movies[Actor] ) && Movies[IMDb ID] <> EARLIER ( Movies[IMDb ID] ) ) ) > 0 && Movies[Actor] <> SELECTEDVALUE ( 'Actors Worked With'[Actor A] ) ) )

1. In the "Movies Worked Together" column, use the COUNTROWS and FILTER functions to count the number of movies each actor has worked together with the selected actor. You can use a formula like this:

Movies Worked Together = CALCULATE ( COUNTROWS ( Movies ), FILTER ( Movies, Movies[Actor] = SELECTEDVALUE ( 'Actors Worked With'[Actor B] ) && Movies[IMDb ID] IN ( FILTER ( Movies[IMDb ID], Movies[Actor] = SELECTEDVALUE ( 'Actors Worked With'[Actor A] ) ) ) ) )

1. Join the "Actors Worked With" table with the "Movies" table using the "Actor A" and "Actor B" columns.

As for the bonus question, creating a reference table should not require different steps to create the requested visualization as long as the table relationships are properly set up. However, you may need to adjust the DAX formulas to reference the correct table names.

[Governator]

Thanks for the swift reply! The first formula however doesn't get accepted, it says "Token Literal expected" and points to the first double ampersand (&&)...

Secondly: I should create this new table completely inside PBI? Because the first column Actor A will have to be dynamic and change with new movies being added, so I'd have to reference the Movies table to create that column? Or do I create a second (identical) table in Excel?