cancel
Showing results 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

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	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	country		denmark	2017
4	type		blue	2017
4	size		medium	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
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
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
Employee

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,

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.
6 REPLIES 6
Employee

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,

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.
Post 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?

Employee

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.
Post Patron

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	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	country	o	denmark	2017
3	country d	finland	2017
4	type		blue	2017
4	size		medium	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
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?

Employee

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,

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.
Post Patron

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

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors