Advise on best approach -

[lutho]

I all, I am looking fo some guidance on best practice.

I am creating a student dashboard. I have a big data table that contains data from an annual student test covering multiple years. I can select a student via basic slicer and diplay current subjects and the series of  annual scores.

However I need to show student performance in the context of the other students for that year either as a box or violin plot (ie. Year 5 student vs all Year 5 students in 2013, Year 6 student vs all year 6 students in 2014 etc). The slicer reduces the data set to just the student so the box/violin ends up with one data point.

I can easily create an alternate table using SQL but it seems so wrong to not do this in PBI. What would be the best practice approach? I just need a pointer and can run with it from there.

Thanks in advance.

[westwrightj · Accepted solution]

Hey @lutho 

  As @amitchandak  mentioned it would be best to have some sample data and a sample output to answer this question.

Without this, here is some sample data I have included to try and meet your solution

Also I've gone ahead and duplicate this table in the query editor and paried it down just to unique Student-Year combinations

The tables are connected by year and one filters the other in a one way many to many relationship

Now we can write a measure like this

Students From Same Year as Selected = 

var TheStudent = VALUES('Test Data (2)'[Student])

var TheYear = CALCULATE(MAX('Test Data'[Year]), 'Test Data'[Student] in TheStudent)

return

CALCULATE(MAX('Test Data'[Score]), 'Test Data'[Year] = TheYear)

This will allow us to make a violin chart that will adjust based on the year of our selected student

If you wanted to also see the selected student's score you could add a card with this measure

Student Score = 

var TheSelectedStudent = SELECTEDVALUE('Test Data (2)'[Student])

return

CALCULATE(MAX('Test Data'[Score]), 'Test Data'[Student] = TheSelectedStudent)

Let me know if this helps or if you were looking for different functionality.

[amitchandak]

@lutho ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.