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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors