Forum Discussion

hoegsdal's avatar
hoegsdal
New Member
9 years ago

Filter table based on selection in chart

I'm new to Power BI so this question might have been asked already but I don't know what to search to find the answer.

 

I have build a website with a survey. Each survey has a Yes / No question and then a comment box. An example could be "Did you like the food?" the user selects Yes or No and can write a comment.

 

I would then like to build some reporting around this. Firstly I would like to show how many users selected "yes" or "no" over time and when selecting a value then show the comments.

 

This is my table:

 

 

The field QuestionId contains either 1 or 2. 1 is linked to the Yes / No question, i.e. "Did you like the food?" and 2 is linked to the comment.

The field PossibleAnswer contains either "Yes", "No" or the text of the comment.

The field Count conatins either 0 or 1. 0 if the user selected No, 1 if the user selected Yes and always 1 related to the comment.

The field FeedbackId is the common id for the survey, i.e. related to the yes/no question and the comment.

The field DateId is linked to a Date table with day, month and year details.

 

Example of data:

 

The reason why I have "No" in PossibleAnswer with Count set to 0 is because I want to show the possible answers in the chart. Possible answers could be something else than "Yes" and "No", e.g. "Good", "Okay", "Bad".

 

First I built a stacked column chart which displays the answers to the question "Did you like the food?" over time. This chart is filtered by QuestionId = 1 as I only want to show the Yes/No value not the comments. The chart looks like this:

 

 

So far so good. I now want to click on "No" for October and see all the comments related to this selection. As you can see below I have selected "No" for October and there are 9 people who have answered No to the question "Did you like the food?".

 

 

 

I would then like to show a table with all the comments related to this selection, i.e. where the user answers No and it's in the month of October.

 

When I try to create a table showing the comments I can get all the related records but not show the text of the comment. It's just showing "No". My initial thoughts are that I can use the FeedbackId as this is a common id for the question and the comment but I'm not sure how to get the list of Feedback Ids and then use these to filter a table. This might not even be the right approach.

 

This is the result of the table with comments. As you can see I only get the text "No" for all the comments.

 

 

This doesn't seem like a hard thing to achieve but I'm new to Power BI and haven't tried this before.

 

 

 

Need Help

1 Reply

  • mike_honey's avatar
    mike_honey
    Memorable Member
    9 years ago

    It sounds like (from a modeling perspective) you need 2 tables - one with the QuestionId = 1 rows (yes/no), the other with the QuestionId = 2 rows (comments).  I would build this by editing the queries - I would create 2 Queries by "Reference" to your existing Query, with filters on each as per the previous sentence.

     

    Then I would try to create a relationship between the 2 new tables.  The column chosen needs to be a unique key in at least one of the 2 tables.

     

    With all that in place, you should be able to combine columns from the 2 new tables, with the relationship managing the cross-filtering.