Show all available answers for survey question, even answers that haven't been given

[Danro01]

My situation is the following. I have a datamodel containg 3 tables:

- fct_responses

- dim_questions

- dim_answers

My fct table is related to both the question and answer table use a questionId and anAnswerId. These are one-to-many relations as they should be:

Now, I want to create a bar chart showing the question text (from dim_questions), all answer options (from dim_answers) and the amount of times they appear. (from fct_responses). This causes me problems when certain answer options aren't present within the fact table because they haven't been given.

Take the question: How would you rate your customer experience?

The answers range from 1 till 5.

Respondents answered 4 and 5. The answers 1,2 and 3 haven't been given.

How can I still make sure that the answer values 1,2 and 3 are also shown within a visual? Just with 0 responses.

[Danro01 · Accepted solution]

I think I managed to fix this. I created a little snowflake setup:

[v-sshirivolu]

Hi @Danro01 ,

Glad to hear that. It seems you have resolved the issue. By restructuring the data model into a small snowflake setup, the Question slicer now correctly filters the related Answer options, showing all choices in the visual, including those with zero responses.

This method is more efficient than merging tables and aligns with best practices for managing question-answer hierarchies in Power BI.

[Danro01]

@johnbasha33 @GeraldGEmerick Thanks for the detailed instructions to both of you! Both suggestions didn't really give me the desired result. They showed all answers for all questions, ignoring the slicer selection on the question field from dim_question.

I've created a tiny sample dataset to show the issue i'm facing. Maybe that explains the point better. In the current situation, it shows all answers for all questions. Instead of all answers for the selected question.

https://drive.google.com/file/d/1iod5-yl31V9MGlCDSw9WSnvpb2hbOn8m/view?usp=sharing

Next to that, I have a broader question.
Currently I have a seperate table containing the questions and a seperate one for the answers. Would it be better to just merge them in our data warehouse? To create one big table containing all questions and all of their answer options?

I splitted them out since their grain is different and I though it would be more efficiënt. One big table would cause more repeated values.

The sole purpose of the dim_questions table right now is to present questions and slice on them.

[johnbasha33]

Hi @Danro01 

Power BI visuals automatically apply an inner join between your dimension and fact tables.
So if no fact rows exist for AnswerId = 1, that answer simply doesn’t appear in your chart.

You want to force all answer options to show up, even with 0 responses.

The fix: use a measure, not a column count

Step 1: Create a measure that counts responses safely

Response Count =
COUNTROWS(fct_responses)

or, if you need distinct respondents:
Response Count =
DISTINCTCOUNT(fct_responses[ResponseId])

Step 2: In your bar chart:

• X-axis: dim_answers[AnswerText]

• Legend or small multiple: dim_questions[QuestionText]

• Values: Response Count

Step 3: Adjust visual interaction

Make sure the question being analyzed filters dim_answers correctly (e.g., via QuestionId relationship).
Then do the key part:

In the Visual → Filters pane, click the “Response Count” field, and uncheck “Show items with no data” → TURN IT ON.

Optional: handle with DAX instead of visual setting

If you prefer to do it in a measure explicitly, use:
Response Count =
VAR Responses =
COUNTROWS(fct_responses)
RETURN
COALESCE(Responses, 0)
Or even better, if you suspect crossfiltering issues:
Response Count :=
VAR SelectedAnswer = SELECTEDVALUE(dim_answers[AnswerId])
RETURN
CALCULATE(
COUNTROWS(fct_responses),
KEEPFILTERS(fct_responses[AnswerId] = SelectedAnswer)
)
This guarantees that every answer in dim_answers will produce a value (0 if missing).

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[GeraldGEmerick]

@Danro01 One method would be to duplicate your dim_answers table and make sure there are no relationships, dim_answers2. Use the question_option_text column from your dim_answers2 table in your visual and create a measure that similar to the following:

Number of Answers =
VAR _Answer = MAX( 'dim_ansers2'[pk_question_answer_id] )
VAR _Return = CALCULATE( COUNTROWS( 'fct_repsonses ), FILTER( 'fct_responses', [fk_question_answer_id] = _Answer ) )
RETURN _Return