Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
grggmrtn
Post Patron
Post Patron

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 😄

 

I hope someone knows just what I need to do 🙂

 

1 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 )
    )

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-cherch-msft
Microsoft Employee
Microsoft 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 )
    )

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 )
    )

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.