join calculated tables?

[Betsy]

Hi,

I've created two calculated tables: One to summarize questions received by students (screenshot):

And one to summarize questions answered (screenshot):

In order to calculate the percentage answered in the second calculated table I need to use the values in the first calculated table

[v-haibl-msft]

@Betsy

Not sure if you’re look for this. Since we have relationship between Questions Answered and Questions Received, we can use RELATED function.

Percent Replied = 
'Questions Answered'[Total Replied]
    / RELATED ( 'Questions Received'[Total Received] )

Best Regards,

Herbert

[Betsy · Accepted solution]

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!!

[jahida]

I think the simplest thing for you will be to merge the two calculated tables you have into one before doing any other calculations. Can you maybe try doing something like:

DISTINCT(UNION(SELECTCOLUMNS('question table', "Package ID", 'question table'[Package ID]), SELECTCOLUMNS('response table', "Package ID", 'response table'[Package ID])))

to give you all Package IDs in both tables, and maybe something like this for the whole formula:

ADDCOLUMNS(

DISTINCT(UNION(SELECTCOLUMNS('question table', "PackageID", 'question table'[Package ID]), SELECTCOLUMNS('response table', "PackageID", 'response table'[Package ID]))),

"Total Received", COUNTROWS(FILTER('question table', 'question table'[Package ID] = [PackageID])),

"Total Responses", COUNTROWS(FILTER('response table', 'response table'[Package ID] = [PackageID])))

That might make it easier to do calculations bewteen the numbers, and I don't think having two summary tables here really makes sense. Hope that helps!

[a_mixed_life]

Sorry if I'm missing it, what is the question actually? Which column are you trying to calculate or join? When you say 'Join', do you mean creating a relationship?

You would just create a relationship using the Package ID as the key column.

[Betsy]

@a_mixed_life

Thanks for looking at this. Looks like my full explanation was cut off when I posted. Not sure what happened there.

I am trying to create a calculated column in a calculated table by dividing a column from one table by a column from another.

Table A: Questions Received

Column 1: Package ID     Column 2: Total Received     Column 3: Percent Received

1                                   50                                     20% [Total Received]/[Student Count] (A calculated measure already done)

2                                   12

3                                   3

4                                   40

These columns tell me how many people received each question. They are calculated using SUMMARIZE from a query table.

Table B: Questions Answered

Column 1: Package ID     Column 2: Total Replied      Column 3: Percent Replied

1                                   5                                      ?? (How do I get this? I know it is Total Replied/Total Received)

2                                   3

4                                   2

These columns tell me how many people answered the question. They are calculated the same way as in Table 1, but the source table is different.

I need to say for each question, what percent of people replied. So, I need Total Replied/Total Received. For question 1, that would be 5%.

I cannot do a straight calcuated column with Percent replied = 'Questions Answered'[Total Responses]/'Questions Received'[Total Received]. There is a relationship set between the two calculated tables using Package ID. I get an error saying: "A single value for column "Total Received" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation".

I was trying to merge the two tables using NATURALINNERJOIN('Questions Received','Questions Answered), which would take out the questions that weren't answered, but I have those in the source table. I get an error saying: "The column with the name of Package ID (which is what the join is made from) already exists in the table (that I am trying to create in the merge)".

Thanks again!

Betsy

[itchyeyeballs]

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:

1. Revise your data model by looking again at how you are designing your overall table structure.
2. 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).
3. 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])

[Betsy]

@itchyeyeballs

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!!