Forum Discussion
MS Forms and Power BI Slicer
Hello. I have set up a Microsoft Form to collect several elements. There is branching within the form based on information that is entered. I am trying to set up a Slicer in Power BI that will limit the information displayed to only the elements from the MS Form that are applicable based on the way the branching was set up.
Example: Q1 has 5 options. If Option 1 is selected, user is directed to fill in Q2-Q6, If option 2 is selected, user is directed to fill in Q7-Q10, etc.
Are there any recommendations on how I can get the slicer to display only the questions/responses that are appropriate based on the selection from Q1?
Hi,
What you’re running into is a common limitation—Power BI doesn’t understand Microsoft Forms branching logic automatically, because all questions (Q2–Q10, etc.) still exist in the dataset even if they were never shown to the user.
To handle this, you need to recreate the branching logic in your data model.
A practical approach is to add logic that identifies which questions are actually relevant based on the answer to Q1. For example, you can create a calculated column or measure like:
- If Q1 = Option 1 → mark Q2–Q6 as “Valid”
- If Q1 = Option 2 → mark Q7–Q10 as “Valid”
Then use this “Valid/Relevant” flag as a filter for your slicer or visual. This way, only the applicable questions/responses will be shown.
Another approach is to reshape your data (unpivot it in Power Query) into a format like:
- Respondent ID
- Question
- Answer
- Applicable Flag
Once the data is normalized like this, you can easily use a slicer on Q1 and filter visuals to show only rows where the question is applicable.
Hope this Helps!!
Thanks!
3 Replies
- SamInogic
Super User
Hi,
What you’re running into is a common limitation—Power BI doesn’t understand Microsoft Forms branching logic automatically, because all questions (Q2–Q10, etc.) still exist in the dataset even if they were never shown to the user.
To handle this, you need to recreate the branching logic in your data model.
A practical approach is to add logic that identifies which questions are actually relevant based on the answer to Q1. For example, you can create a calculated column or measure like:
- If Q1 = Option 1 → mark Q2–Q6 as “Valid”
- If Q1 = Option 2 → mark Q7–Q10 as “Valid”
Then use this “Valid/Relevant” flag as a filter for your slicer or visual. This way, only the applicable questions/responses will be shown.
Another approach is to reshape your data (unpivot it in Power Query) into a format like:
- Respondent ID
- Question
- Answer
- Applicable Flag
Once the data is normalized like this, you can easily use a slicer on Q1 and filter visuals to show only rows where the question is applicable.
Hope this Helps!!
Thanks!
Thank you for clearly outlining the options and providing details. I am luck enough that I am early in the process of building the forms and the dashboards so I was able to step back and rethink how the data was going to be collected to support the ability to report it out.
- krishnakanth240
Hi Sully
Power BI slicers cannot dynamically show or hide fields based on form branching logic. You can achieve it by modeling that logic in the data by creating a mapping between Q1 options and the corresponding valid questions either via a calculated column or a separate mapping table and use Q1 as a slicer to filter the dataset so that only relevant questions and responses appear in visuals.
