Forum Discussion
Complex: Filtering data by name
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 ca...
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:
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