Need guidance on achieving desired filter results

[cmdrvimes]

Hello all, I am working on a dataset in which I have a field that can contain multiple values, delimited by a comma ("Role" field in below sample). 

Example dataset:

Person	Role
Jack Nicholson	Actor
Marlon Brando	Actor
Robert De Niro	Producer, Actor
Meryl Streep	Director
Denzel Washington	Actor, Director
Al Pacino	Actor
Tom Hanks	Producer
Leonardo DiCaprio	Actor, Director
Brad Pitt	Producer, Actor, Director

(etc.)

On my report, I would like to display the list of people and roles, as well as a table of the roles with a count of how many people fulfill those roles:

I was able to achieve the first step of the process, which was getting a list of the distinct roles, and generated the table on the right, by following instructions from this post: Solved: Re: PBI - split multiple value to rows in a table - Microsoft Fabric Community

By doing so, I created a duplication of the original dataset, which now looks like this:

Person	Role
Jack Nicholson	Actor
Marlon Brando	Actor
Robert De Niro	Producer
Robert De Niro	Actor
Meryl Streep	Director
Denzel Washington	Actor
Denzel Washington	Director
Al Pacino	Actor

(etc.)

Now I have two tables in my PBIX file with no relationship between them. 

Desired Outcome: I want to click on a role in the table on the right (ex: Actor) and have the table on the left filter to show me all of the people with that role type. 

What do I need to do to achieve this functionality? 

Power BI automatically set up a 1:M relationship between Actors:Actors(2) but that didn't work. Creating a M:M relationship between the two tables also does not result in the correct filtering performance.

I am not sure what the next step is, or what search terms to use to figure this out. Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

Hi @cmdrvimes ,

Thank you for @HotChilli answer , and I have other solution for you:

Firstly , you can create a flag measure to filter the data:

Flag = IF( CONTAINSSTRING(MAX('Table'[Role]),MAX('Actors(2)'[Role])),1,0)

Then,the final output will be like this:

Best Regards,

Xianda Tang

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

[HotChilli]

I hope you used Ashish's solution in that other thread.  The other ones are over-complicated.

--

OK, you should have a table that is in a good format. Get rid of the original table (or don't load it to the model).

Create another table with just the roles in it. This is the dimension table. Connect it 1:m with the (single) Fact table.

--

Now write a couple of measures (one with CONCATENATEX

https://learn.microsoft.com/en-us/dax/concatenatex-function-dax?WT.mc_id=DP-MVP-4025372 )

and one with COUNT

--

Now set up your visuals (possible a slicer with the roles from the role dimension and  a couple of tables as in your post above)

--

So, for a beginner, that's enough to get stuck in to but that should set you on the right track

[cmdrvimes]

Yes, I did use the split to columns option to get the 2nd table. 

In my real-world use case, I do need to keep data from other columns in the original table, but now you've got me thinking about better ways to model the data. Maybe I need to split it out into some additional dimension tables.

I'll try out your suggestions, thank you.