Forum Discussion

grggmrtn's avatar
grggmrtn
Icon for Post Patron rankPost Patron
7 years ago
Solved

Complex filtering problem

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 :D

 

I hope someone knows just what I need to do :)

 

  • 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,

6 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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's avatar
      grggmrtn
      Icon for Post Patron rankPost Patron

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee

        Hi grggmrtn 

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

        Regards,