Forum Discussion
Complex filtering problem
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,
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?
Hi grggmrtn
I'm afraid i cannot fully understand it.Could you share the example and post some data and expected output here?
Regards,
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?
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,