Best way to model multiple many-to-many relationships for filtering

[daveggw]

I have source data that contains Question and Answer Data where the Q&A are of the multi select type. I also have multiple different questions. These questions and answers need to be used to filter records for people in a dimension table.  In source my Q&A data appears as 3 columns. A key (to person) plus the question and answer pair. I can therefore separate out each question. However, since the questions are multi-select there can be many answers to the same question for a given person. I can't figure out the best way to model this. The attached picture shows my current model, it allows me to filter by different combinations of questions and answers. It seems to work OK but my main concern is that as it's a many-to-one relationship and I need to enabled bi-directional filtering if I'm presenting the Q & A as slicers. This concerns me becuase bi-directional filtering is generally not recommended. I would add that I can't (easily) de-normalise my Q&As into a single dimension table with a column for each posible answer and a true/false flag. This is becuase the possible answers for each question will change regularly over time which would mean the columns in the dimension would need to be change and become quite wide. I'm trying to figure out if there is a better way of modelling this. Intuitively I feel there should be a better way but I can't figure it out for myself and I can't find an example of a similar scenario and solutions. It really does feel like it should be a problem that is not uncommen and has been solved many times over. Who can suggest a better way? Thanks in advance

[v-zhenbw-msft]

Hi @daveggw ,

Are Q&A columns from your Who I want to meet table?

Do you want to create a slicer using the fields in this table? And you want to filter the fact table using the Q&A slicer?

Generally speaking, both relationship can solve your issue.

Could you please provide a mockup sample based on fake data?

The Q&A table, Person table and interaction table are enough.

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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