Advice on setting up a recursive question answer structure with link to dimension table in PowerBI

[Anonymous]

Hi, first I'll paint a picture of what I'm trying to do and give some example data to try and emphasise it.

I have a table which contains millions of rows. Each row contains a customerID and Answer_key, I did also set it up that it could link the next question from the hierarchy if applicable.

This answer key links to a dimension table which contains the information related to this Answer_key, such as QuestionID, QuestionText, AnswerText, AnswerCode, BaseQuestionIndicator  and Answer_key which is the Primary Key of this table.

The idea of what I'd like to be able to do is have a base question in powerbi and recursively go through all the child questions of this base question. Mapping the route that clients took through the application to see how different question sets were answered. The issue I'm having is there there are potentially hundreds of thousands of different routes and questions could go down to a very deep level (20+) sub-questions and each client will have a different list of answered questions which could vary from 40 - 150. Due to the way in powerbi I can only have one active relationship between two tables. I am unsure exactly how to set it up so that I could be expandible in the way I want.

If I set the dimension table up to have BaseQuestion, Next Question, FinalQuestion. All questions greater than set 3 dont have an expandible path between them.

Examples of how my tables look are as follows: This is the Fact table which could contain Dim_AnswerKey_Next if that would help things (which only contains a next ID if it is next in the hierarchy and not another base question)

CustomerID	Dim_AnswerKey
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
1	9
1	10
2	1
2	11
2	12
2	13
2	14
2	15
2	16

Then the Dimension table would look something like, with arbitrary values.

Dim_AnswerKey	QuestionID	QuestionText	AnswerText	BaseQuestion
1	1	she	there	1
2	2	sells	once	1
3	3	sea	was	1
4	4	shells	a	1
5	5	on	man	1
6	6	the sea	named	1
7	7	shore	bob	1
8	8	but	who	0
9	9	it's	ate	0
10	10	snowing	pineapples	0
1	1	she	there	1
11	2	sells	never	1
12	3	sea	were	1
13	4	shells	any	1
14	5	on	apples	1
15	6	the sea	by	1
16	7	shore	night	1

In this instance for the first customer, would could see that there were a few subquestions to question 7 that were answered based on the fact that there were some answerkeys for that client which were NOT a base question. I would like to create a visual which would be able to show that in this example two clients answered question 7, then after that one client answered question 8,9,10. By expanding on that hierarchy as it were.

All I managed to do previously was have a dimension table like:

Base Question, Question, Dim_Answerkey. Which just showed all the possibilities from the base question but didn't include the sub-question hierarchy. I could then summarise on the number of clients who had that answerkey and could see where it originated from (in terms of base question) but not how it got from base question to final question through the sub-question hierarchy.

I don't really see a way of doing this personally and any help and insight would be greatly appreciated. If there is any other information that I can add to make this more understandable - please let me know 🙂

[v-lionel-msft]

Hi @Anonymous ,

I don’t really understand what you want. Could you show the desired output?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft , What I would like is to be able to navigate through the question heirarchy to see how a particular question set was answered.

For example. the first question is a yes/no question. If they answer no - we move on with the users application, if they answer yes, we drop down another subquestion. Each of these have their own key combination. So each unique question and answer has a unique key as the primary key (Let's call it questionlink) in the dimension table. This table is stored in a parent heirarchy fashion where each key points to its parent key (one issue I have here is one key could have multiple parents). The fact table contains all the questionlink ids from the dimension table that a particular client answered. My end goal would like to be a visual where the end user can see the base yes/no question and the number of clients that client yes and no. In a matrix tabular form they would then be able to click the + to navigate through the question tree to see how the number of clients answering each path through the tree varied. Does this make a bit more sense?

[lbendlin]

Are you trying to report on actual recorded customer behavior, or is that a hierarchy of potential Q&A ? Does each item have a unique identifier?  Have you considered using parent-child hierarchies?

[Anonymous]

Hi @lbendlin , yes I have considered using parent child hierarchies. But they weren't really returning what I was after. Every question answer combination has a unique key. However this unique key can come from multiple parents which was my first problem using parent child heirarchies that one child could have multiple parents. I tried implement various flattener questions for flattening the data but these were unable to run on my large amounts of data - ran fine on very small subsets. I always tried implementing the following technique: Parent-child hierarchies – DAX Patterns. But my issue here was that they were counting money whereas I wanted to count clients who answered that combination and linked the dimension table to the fact table for this I was unable to do.