Forum Discussion
join calculated tables?
I tried merging the question and responses tables first, as a couple people mentioned is probably best practice, but it wasn't coming out correctly, more rows than either original summarized calculated table, as if the merge was not filtering correctly. Stuff that people hadn't "received" was being marked as "responded" (I think non-questions, even with the filter I describe below) What finally worked:
Merging comm type from communications table into the responses source table, and filtering on equals = question (I had already filtered out Package ID-less responses).
Keeping the summarized tables, Questions Received and Questions Answered.
Using the related function to create a column for Total Replied in the Questions Received table. Then calcuating the percent.
Thanks everyone!!
Hi Betsy,
I'm interested in why you have created the calculated tables, can you share what your underlying data looks like?
I have a hunch you may be better off rethinking your data model to achieve what you need and reduce the complexity. I can think of 3 possible ways forward:
- Revise your data model by looking again at how you are designing your overall table structure.
- Similar to 1, keep the two calculated tables but dont join them directly, instead create a third Package_ID table (just a dimension table listing all your packages) and link both your calculated tables to that (and not each other at all). you can then cross reference calculations against each table as long as you use the Package_ID table fields as your dimension (column/row labels).
- If you do need to keep your structure as is you might want to use the related function to created new columns in table A which you can then calculate on more easilly, smethign like related(TableB[total Replied])
My data consists of 5 different source csv files, that look like this (very simplified):
Table 1. Students
Student ID;Start Date;Gender;Active
1 | 9/1/2015 | F | TRUE |
2 | 1/15/2016 | F | TRUE |
3 | 10/1/15 | M | FALSE |
Table 2. Packages
Package ID;Student ID;Comm Type;Sent
1 | 1 | question | 9/6/2015 |
2 | 1 | statement | 9/7/2015 |
4 | 2 | story | 1/15/2016 |
Table 3. Responses
Package ID;Student ID;Text;Sent
1 | 1 | Yes | 9/6/2015 |
| 3 | Stop | 10/1/2015 |
3 | 4 | 5 | 1/15/2016 |
Table 4. Communications
Package ID;Comm Type;Message Body;Date added
1 | question | What do you like… | 9/1/2015 |
2 | statement | Some people… | 9/1/2015 |
3 | question | When we make.. | 9/30/2015 |
Table 5. Questions
Package ID;Message Body;Question Type;Date added
1 | What do you like.. | Pick a choice | 9/1/2015 |
3 | When we make… | 5 star | 9/1/2015 |
5 | Who is your… | Open ended | 9/6/2015 |
In the Package and Response tables if a specific package wasn’t sent to, or a reply wasn’t made by, a specific student there is no row for that combination. When looking at responses, you can’t tell from the raw data in Responses if student 2 didn’t answer question 1 because they were no longer in the system, because they didn’t receive question 1, or because they received ques. 1 but didn’t reply.
Germane to what I’m trying to do now, I had created 2 duplicates of the student tables and one each of the Packages and Responses tables filtering out stuff I didn’t need now, but do need otherwise (like statements and stories from the Packages table, or Text with no Package ID from the Responses table). Then I merged and pivoted each one, so that every student will have a record that can be summed. For each student, we can see which packages they received and which they replied to.
I summarized the question and responses tables that I duplicated for the pivot using the calcuated tables I described above, so that we could see at the question level what we can already see at the student level:
Question Total Received Percent Received
1 50 10% (50/500 students)
Etc. as I laid out above. No doubt I could simplify the (this is my first Power BI experience), but I don't know how.
For now, I'll play around with option 3, and report back.
Thanks again!!