Complex: Filtering data by name

[MurugeshC]

Hi,

I have a list of names in the filter and if I select a name from the filter then all the records with the selected name should be filtered out.

For example, in a table there is a field called "Attendees" and there are multiple names for each record as shown below.

Then in the filter, if we select a name like;

Then the table should filter all the records with the selected name as shown below:

How can I fix the filter?

I hope my question is clear. If not please let me know, I will try to draft again. 

Thanks,

Muru

[Anonymous]

@MurugeshC ,

I went ahead and did this using power query, so I attached the pbix file below. But I went off using this example:

Project	Fruits
A	Apple, Pear, Lime
B	Apple, Orange, Grape, Strawberry
C	Grape
D	Lime, Strawberry

After loading that table into PowerQuery:

• Split the "Fruits" column by delimiter and to new rows, not columns. giving this table
• 
• Group this new table by project and aggregate all data
• Add a new column wiht this code

List.Accumulate( [All Data][Fruits],"", (state,current) => state &","
& current )

which produces this column:

Expand that table out

Do some misc clean up and you get this final table

Then just to make the data model complete, created a dimFruits and DimList table which would be used to filter the above table

Final output

File:

https://1drv.ms/u/s!Amqd8ArUSwDS1TYkrRtY1efnZd8m

[MurugeshC]

Dear @Anonymous,

Your suggestion works fine on the direct query mode. But I want to fix the filters on Live Connection, how can I do that? Please advise.

Thanks.

[Anonymous]

@MurugeshC 

I'll  have to mess around with Live Connection and see

[MurugeshC]

Thank you for your reply, @Anonymous 

I am using Live Connection (SSAS Model). I know there are some limitation on the Live connection like accessing Query Editor, etc.

Can you please tell me how can we fix this filtering issue on Live Connection?

Thanks,

Muru