Create a SUMMARIZE table from related source

[SPaine86]

Hi everyone!

I am trying to create a SUMMARY table (of 'current data') which I can then append to 'historic data' using Union.

The issue I am having is that one of the columns I need to group by is in a related table, and it doesn't appear as an option when I start typing. If I force it in, I get the red underline which tells me that what I am doing is wrong.

I can achieve what I want quite easilyy in a table or matrix visualisation, so I believe it should be possible to do?

To simplify, I have three tables: Pupils, Attendance, and Group Memberships.

Each pupil has a one to many relationship with Attendance.

Each pupil has a one to many relationship with Group Memberships (the filter is both directions)..

Pupils contains:

UID (Unique ID)

Name

DoB

Pupil Year Group

etc

Attendance contains:

UID

Academic Year

Term

Number of sessions present

Number of sessions possible

Group contains:

UID

Characteristic Group

The summary table I want to create (called 2021 Attendance) will contain the following columns:

(Grouped by)

Academic Year

Term

Pupil Year Group

Characteristic Group

(Calculations)

Cohort (sum of UIDs)

Attendance (sum of number of sessions present / sum of number of sessions possible)

2021 Attendance = SUMMARIZE('Attendance', 'Attendance'[Academic Year], 'Attendance'[Term], 'Pupils'[Pupil Year Group], 'Group'[Characteristic Group], "Cohort", distinctcount([UID]), "Attendance", divide(sum([Present]), sum([Possible],0))

Can anyone see where I'm going wrong?

Thanks

Steve

[amitchandak]

@SPaine86 , Can you share sample data and sample output in table format?

But refer if this can solve the purpose

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[SPaine86]

I am sorry that I did not provide enough clarity.

OK so here is some sample information:

PUPILS table

UID	Name	DOB	Pupil Year Group
A123	Bob	1/3/96	Year 3
A234	Sally	2/4/97	Year 4
A345	Dave	3/5/96	Year 3
A456	Abdul	4/6/96	Year 3

ATTENDANCE table

UID	Year	Term	Present	Possible
A123	2021	Term 1	50	100
A234	2021	Term 1	75	100
A345	2021	Term 1	100	100
A456	2021	Term 1	90	100
A123	2021	Term 2	95	100
A234	2021	Term 2	100	100
A345	2021	Term 2	100	100
A456	2021	Term 2	95	100

GROUP table:

UID	Characteristic
A123	Left handed
A123	Black African
A123	Disadvantaged
A123	Born in Summer
A234	Right handed
A234	White British
A234	Not Disadvantaged
A345	Black African

etc

Each pupil is related to multiple groups.

I want to produce a summary table which looks like:

Year	Term	Pupil Year Group	Characteristic	Cohort	Attendance
2021	Term 1	Year 3	Left handed	15	80.4
2021	Term 1	Year 3	Black African	13	90.8
2021	Term 1	Year 3	Disadvantaged	5	37.7
2021	Term 1	Year 3	Born in Summer	2	74.5
2021	Term 1	Year 3	Right handed	1	80.6
2021	Term 1	Year 3	White British	27	79.5
2021	Term 1	Year 3	Not Disadvantaged	31	etc
2021	Term 1	Year 4	Left handed	13
2021	Term 1	Year 4	Black African	18
2021	Term 1	Year 4	Disadvantaged	6
2021	Term 1	Year 4	Born in Summer	etc
2021	Term 1	Year 4	Right handed
2021	Term 1	Year 4	White British
2021	Term 1	Year 4	Not Disadvantaged

The issue I have is adding the column 'Characteristic' to my SUMMARIZE function when I create a new table.

The model is shaped like this:

Pupil to Group - One to many (filter in both directions)

Pupil to Attendance - One to many (filter left to right)

Other tables which are related to the Pupil table do appear to work, but I am unable to add Characteristic into the summary. I have tried RELATED('Group'[Group]) but this is not recognised ("Function SUMMARIZE expects a column name as argument number").

Is this more helpful?

[Greg_Deckler]

@SPaine86 - Maybe use RELATED or RELATEDTABLE. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.