Dynamically display sections and their details based on selected audit in Power BI

Let’s assume you have the following tables:

Table	Key Columns
Auditorias	auditoria_id
Secciones	seccion_id, nombre, auditoria_id
Preguntas	pregunta_id, texto, seccion_id
Respuestas	pregunta_id, respuesta, auditoria_id (or linked indirectly)

1. Add a slicer for auditoria_id

Place a slicer on the page using the auditoria_id field from your Auditorias table.

2. Create a calculated column for grouping display

If you want to show section headers within a table or matrix visual, Power BI doesn’t support visual-level grouping like that. But we can simulate it.

Create a calculated column in your Preguntas table:

DisplayText =

"[" & RELATED(Secciones[nombre]) & "] " & Preguntas[texto]

This gives you:
[SECTION 1] What is your name?
[FINAL SECTION] Any comments?

3. Use a Table Visual (Simulated Header Grouping)

In a Power BI Table visual:

• Add Secciones[nombre] as the first column
• Then add:
• Preguntas[texto]
• Respuestas[respuesta]

Apply sorting by:

1. Secciones[nombre]
2. Preguntas[texto]

Then it will group by section automatically (with no manual visuals), like this:

Section Name	Question	Answer
SECTION 1	What is your name?	John
SECTION 1	Age?	25
FINAL SECTION	Any comments?	None

Use Matrix Visual for Collapsible Sections

If you want collapsible sections:

• Use a Matrix visual
• Rows:
• Secciones[nombre]
• Preguntas[texto]
• Values:
• Respuestas[respuesta]

Now, each section becomes expandable/collapsible automatically.

 

Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.

 

Let us assume you have four tables: Audits:

AuditID (PK)	AuditName	Date
1	Safety Audit	2024-01-15
2	Compliance Check	2024-02-03

 Sections:

SectionID (PK)	AuditID (FK)	SectionName
10	1	General Info
11	1	Final Comments
12	2	Compliance Items

Questions:

QuestionID (PK)	SectionID (FK)	QuestionText
100	10	What is your name?
101	10	What is your role?
102	11	Any final remarks?

Answers:

AnswerID (PK)	QuestionID (FK)	AuditID (FK)	Answer
1000	100	1	John Smith
1001	101	1	Inspector
1002	102	1	All good

 

Then, make sure your data model has these relationships: Audits[AuditID] -> Sections[AuditID], Sections[SectionID] -> Questions[SectionID], Questions[QuestionID] -> Answers[QuestionID]

 

If answers are tied to a specific audit, include AuditID in the Answers table and link it to Audits[AuditID]. All relationships must be active and properly defined in Power BI's model view. Create the below DAX measure:

AnswerText =

CONCATENATEX(

    VALUES(Answers[Answer]),

    Answers[Answer],

    ", "

)

 

Add a slicer to your report using Audits[AuditID]. This filters the report to show only data related to the selected audit. Then, insert a Matrix visual. In the Rows, add Sections[SectionName] followed by Questions[QuestionText]. In Values, add the AnswerText measure. Finally, go to the Matrix formatting pane and enable “Stepped layout” under Row headers to clearly indent questions under each section.

 

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.