Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 🙂
Solved! Go to 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,
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,
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,
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |