Complex filtering problem

[grggmrtn]

Our data comes from a questionnaire with the columns [Person] (who filled out the questionnaire), [Question], [Answer], and [Year] (when the question was answered.  It looks something like this:

Person	Question	Answer	Year
1	Color		red	2017
1	type		apple	2017
1	size		big	2018
1	smell		bad	2018
1	country		denmark	2018
2	color		blue	2017
2	size		big	2017
2	smell		good	2018
2	country		sweden	2018
3	Color		red	2017
3	type		pear	2017
3	size		small	2018
3	smell		bad	2018
3	country		denmark	2017
4	type		blue	2017
4	size		medium	2018
4	smell		bad	2018

I've created a matrix visualisation that shows the number of [Person] who gave what [Answer] to which [Question] in what [Year]. Pretty basic.

Question	Answer	Count of persons
Color
		red	2
		blue	2
Type
		apple	1
		pear	1
size
		big	2
		small	1
		medium	1
smell
		bad	3
		good	1
country
		denmark	2
		sweden	1

But I need to filter those results, either with a slicer or just by clicking a [Answer] in the matrix, and the results would show me "for those people who answered [Answer], what else did they answer to the other questions?"

So, using the example table above, if I click on "red", then we'll see that only person 1 and 3 answered "red". The results should then be:

Question	Answer	Count of persons
Color
		red	2
Type
		apple	1
		pear	1
size
		big	1
		small	1
smell
		bad	2
country
		denmark	2

I've tried everything I know how, but unfortunately I only get as far as filtering everything away 😄

I hope someone knows just what I need to do 🙂

[v-cherch-msft]

Hi @grggmrtn 

You may create a slicer table and then create a measure like below:

Slicer = DISTINCT(Question[Answer])

Measure = 
VAR a =
    CALCULATETABLE (
        VALUES ( Question[Person] ),
        FILTER ( ALL ( Question ), Question[Answer] IN VALUES ( Slicer[Answer] ) )
    )
RETURN
    CALCULATE (
        COUNT ( Question[Person] ),
        FILTER ( Question, Question[Person] IN a )
    )

Regards,

[grggmrtn]

Hey, thanks for helping @v-cherch-msft 

Your result works fine, but I have a small problem I forgot to mention (SORRY!).

Some of the answers can be found under several different questions - like if I had Country of Origin and Country of Sale as two seperate questions, and "Denmark" could be found under both questions.

Any help there?

[v-cherch-msft]

Hi @grggmrtn 

I'm afraid i cannot fully understand it.Could you share the example and post some data and expected output here?

Regards,

[grggmrtn]

Hi @v-cherch-msft thanks for your patience 🙂

Person	Question	Answer	Year
1	Color		red	2017
1	type		apple	2017
1	size		big	2018
1	smell		bad	2018
1	country	o	denmark	2018
1	country d	sweden	2018
2	color		blue	2017
2	size		big	2017
2	smell		good	2018
2	country	o	sweden	2018
2	sountry d	denmark	2018
3	Color		red	2017
3	type		pear	2017
3	size		small	2018
3	smell		bad	2018
3	country	o	denmark	2017
3	country d	finland	2017
4	type		blue	2017
4	size		medium	2018
4	smell		bad	2018

Clicking on country o "denmark" which is found in person 1 and 3 will produce:

Question	Answer	Count of persons
Color		red	2
type		apple	1
		pear	1
size		big	1
		small	1
smell		bad	2
country d	sweden	1
		finland	1

Notice "denmark" as the answer to "country d" is not counted among the results, since I need to find it as the result to the question "country o"

Does that make more sense?

[v-cherch-msft · Accepted solution]

Hi @grggmrtn 

You may add a key column and use it as slicer.Then change the measure as below:

Measure = 
VAR a =
    CALCULATETABLE (
        VALUES ( Question[Person] ),
        FILTER ( ALL ( Question ), Question[Key] IN VALUES ( Slicer[Key] ) )
    )
RETURN
    CALCULATE (
        COUNT ( Question[Person] ),
        FILTER ( Question, Question[Person] IN a )
    )

Regards,

[grggmrtn]

That was so simple I should have thought of it myself 😄

But thank you very much @v-cherch-msft !