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

Relationship basics - showing student score averages alongside student score

Hi all,

 

I have 3 basic tables: Student Details, Student Scores, National/State average scores.

 

Link to pbix file and excel source here.

 

I would like to use a slicer to filter to a particular student and see their Scores alongside the State/National for that particular test type. The tests are completed by all students when they are in Year 7 and again in Year 9.

 

Below is an image with my errors highlighted. The table on the bottom left shows the 'averages' table. The table on the bottom right is roughly what I would like to achieve, but of course with incorrect figures shown.

 

Filters not working on exam scores.png

I am unsure if what I am trying to achieve would be better done with a DAX measure? Below are my best efforts structuring the relationships.

 

Relationships.png

Any help would be appreciated.

 

Many Thanks, Doug.

1 ACCEPTED SOLUTION

Accepted Solutions
AnthonyTilley Established Member
Established Member

Re: Relationship basics - showing student score averages alongside student score

Create a colunm in the avarage scores table and the school scores table 

 

Column = 'NAPLAN AVERAGES'[NAPLAN Calendar Year] & 'NAPLAN AVERAGES'[NAPLAN Assessment Area]
 
this will be a unique refrence for the year and assesment type
 
Create a table of unique values 
 
Table = FILTER(DISTINCT('NAPLAN AVERAGES'[Column]),'NAPLAN AVERAGES'[Column] <> BLANK())
Joint the two tables to the joining table as below 
join.png
 
then use the colunm in the new table as the link to get the values correctly 
 
new.jpg

View solution in original post

4 REPLIES 4
AnthonyTilley Established Member
Established Member

Re: Relationship basics - showing student score averages alongside student score

Create a colunm in the avarage scores table and the school scores table 

 

Column = 'NAPLAN AVERAGES'[NAPLAN Calendar Year] & 'NAPLAN AVERAGES'[NAPLAN Assessment Area]
 
this will be a unique refrence for the year and assesment type
 
Create a table of unique values 
 
Table = FILTER(DISTINCT('NAPLAN AVERAGES'[Column]),'NAPLAN AVERAGES'[Column] <> BLANK())
Joint the two tables to the joining table as below 
join.png
 
then use the colunm in the new table as the link to get the values correctly 
 
new.jpg

View solution in original post

dougloader Frequent Visitor
Frequent Visitor

Re: Relationship basics - showing student score averages alongside student score

Hi Anthony,

 

I really appreciate you taking the time to provide a solution.

 

As a beginner, would you be able to explain how I implement the DAX formulas that you have referenced?

 

I have tried to follow your directions, but am unsure of where to place the formulas and what to put in the new 'table of unique values'. I assume that I am doing this in the Power Query Editor?

 

Sorry for this, and thank you!

AnthonyTilley Established Member
Established Member

Re: Relationship basics - showing student score averages alongside student score

to Create the new table 

 

click on the data view tab on the Left hand side

click on modeling in the top menu

click new table 

and enter the code in the Bar

 

newtable.png

 

For the colums

 

go to the data view tabe

right click on the table on right hand side and select new calculated colunm

 

Regards,

Anthony

dougloader Frequent Visitor
Frequent Visitor

Re: Relationship basics - showing student score averages alongside student score

This is awesome. Thanks so much. I really appreciate your time helping me out.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 464 members 4,796 guests
Please welcome our newest community members: