Forum Discussion
Helper ICreate a SUMMARIZE table from related source
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
3 Replies
- Greg_Deckler
Community Champion
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. - amitchandak
Super User
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?
