Filtering a row in a table based upon another row of the same table

[Lumi-Stuart]

Hi all

I'm querying a dataset of answers to custom-built forms. Think of it as two tables:

* FORM

* FORM_ANSWER

The tables contain lots of different forms made up of different questions - some forms may only have a few questions, some may have dozens. The FORM table contains one row per form; the FORM_ANSWER table contains one row per answer per form.

Here is some sample data for three forms, all of the same design template.

FORM:

FORM_ID	FORM	TEMPLATE
1	My Form 1	Template 1
2	My Form 2	Template 1
3	My Form 3	Template 1

FORM_ANSWER

FORM_ANSWER_ID	FORM_ID	QUESTION	RESPONSE_DATA_TYPE	RESPONSE
1	1	Question 1	string	Form 1 answer 1
2	1	Question 2	boolean	TRUE
3	1	Question 3	numeric	12
4	2	Question 1	string	Form 2 answer 1
5	2	Question 2	boolean	FALSE
6	2	Question 3	numeric
7	3	Question 1	string	Form 3 answer 1
8	3	Question 2	boolean	TRUE
9	3	Question 3	numeric	33

I want to be able to create visuals showing responses to different questions that can interact with other visuals for other question.

Example: I want to see the answers for "Question 1" where the response to "Question 2" is true:

Without a cunning plan, if I select "TRUE" responses to "Question 2" in the left-hand visual, the right-hand table filters to no results instead of the two I would like to see (FORM_ID 1 and 3).

I would like to avoid creating a different table for every answer because we're taking about lots of different form templates with lots of answers. I'd also prefer to avoid pivoting the table to give one row per form template.

Is there an approach that can filter the tables in a structure similar to that provided?

Thanks

Stuart

[Lumi-Stuart]

I've provided a link to a sample Power BI report (including the suggestion by @danextian for reference).

Play.pbix 

[danextian]

Hi @Lumi-Stuart 

May possible method but the simplest is to pivot your data so each question will have its own column. In the query editor, duplciate your original query the keep only the form id and question and response columns. Select response then in the transform tab, selected pivot

But you might want to keep the unpivoted format of your data for other forms of analyses like selecting a question from a slicer.

Please see the attached sample pbix.

[Lumi-Stuart]

Thanks for the feedback, but I'd like to avoid pivoting the data if I can. We could be looking at 20+ different templates (i.e. sets of questions), so the only way to practically pivot the data would be to create 20+ pivoted tables (one for each template type).

I'm attempting to build a generic solution (if that's possible).

[v-nmadadi-msft · Accepted solution]

Hi @Lumi-Stuart ,
Thanks for reaching out to the Microsoft fabric community forum.
I've explored several alternative solutions to meet your requirement without using a pivot column, including approaches with DAX measures and slicers. Unfortunately, none of these methods have proven effective in achieving the desired outcome.
As challenging as it may seem, the most viable and reliable solution is to modify your data model to include a pivoted table structure. This approach will provide the flexibility and performance needed for your visualizations to work as intended.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

 

[Lumi-Stuart]

I've ended up compromising and generating a generic pivot of up-to-five questions deep. I.e.:

Q1	A1	Q2	A2	Q3	A3	Q4	A4	Q5	A6
Form type	Form 1	Question 2	True	Question 3	12
Form type	Form 2	Name	James	Other names	False
Form type	Form 2	Name	Gill	Other names	True	What	Gillian

This means that I only have to build a single pivot to cover a range of templates.

It's an unfortunate limitation, though, as it would be a really powerful generalisation solution.

Thanks

[andrewsommer]

Yes, this should be rather straight forward.  Try following these steps:

1. In desktop bring in both tables to your model
2. Create a one way relationship between FORM and FORM_ANSWER on the FORM_ID field
3. Create your visuals
4. In the Ribbon > Formal select Edit Interactions.
   1. Change the interaction to filter from highlight if it is not already on filter
5. The two visuals should not be acting the way you want

Please mark this post as solution if it helps you. Appreciate Kudos.

[Lumi-Stuart]

Thanks for the suggestion, but I can't get that to work. I'm pretty sure that it misses the fundamental issue of the filter item from FORM_ANSWER needs to filter a different row in FORM_ANSWER.

I've attached a version of the Power BI report to another answer. Unless I'm missing something, I don't think that you'll be able to get your steps to return any results.