cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dougloader
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
Super User I
Super User I

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User I
Super User I

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




dougloader
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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors