Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
AnthonyTilley
Solution Sage
Solution Sage

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

5 REPLIES 5
damienstpierre
New Member

Hey Doug, I am just starting out analysing my schools Naplan data and want to do something. Would it be possible to re share your PBIX file/spreadsheet templates so I can have a look.

Cheers

Damo

AnthonyTilley
Solution Sage
Solution Sage

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!




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!

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!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.