Problem with cross-filtering the same table in report.

[DidacBF]

Hello, I am working with questionnaires, so I have a table that simplified looks like this:

It can have like 100 questions.

And the report looks like this:

What I need is that when I filter C In Question 2, in the visual for the Question 1 show me A = 1 AND B = 1, because both visits have those answers when the answer for the question 2 is C. But insted it does this:

So I tried to unpivot the table and have one column for each question, and it would have worked if a question coulnd't have more than one answer, but it can so this is happening:

So, my question is, is it possible to do what i need having all the question / answers within the same table, either the example 1 or the example 2 (unpivoted)? I tried distinct count for visit and all, exclude, etc... But couldn't make it work. It needs to work for all questions at once because the are a lot. What i mean is that i can't exclude in a measure an specific question, because it would be crazy to do that for each one.

If it isn't possible to achieve that with measures, I think it could work if when unpivoting, insted of having nulls it duplicates it's value, like this:

But I don't know how to do that and also, not sure if it's the best solution.

Thank you!!!!

[mlsx4 · Accepted solution]

Hi @DidacBF 

If you use the unpivoted table, you can fill it with the option Fill values in a column: https://learn.microsoft.com/en-us/power-query/fill-values-column

[DidacBF]

Thank you @mlsx4 that worked!

My question now is, you guys think this is a good solution?