cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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)

CustomerIDDim_AnswerKey
11
12
13
14
15
16
17
18
19
110
21
211
212
213
214
215
216

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

Dim_AnswerKeyQuestionIDQuestionTextAnswerTextBaseQuestion
11shethere1
22sellsonce1
33seawas1
44shellsa1
55onman1
66the seanamed1
77shorebob1
88butwho0
99it'sate0
1010snowingpineapples0
11shethere1
112sellsnever1
123seawere1
134shellsany1
145onapples1
156the seaby1
167shorenight1

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 🙂

 

4 REPLIES 4
Community Support
Community Support

Hi @Brownsey ,

 

v-lionel-msft_0-1611726304619.png

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.

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?

Super User III
Super User III

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?

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors