Create a Calculated/Filtered table with DAX

[barbaratate]

Hi all

Firstly, I am a PowerBi Novice - one 'Dashboard in a Day' course and a lot of tinkering.

I'm happy to read and experiment around what I want to do - my query is not how to do it but if it is possible in the first place!! I don't want to invest hours in discovering that I can't do it and I need to find a different way!

My tables have students, details, attendance, subjects, school terms and marks - what I need to do is reorganise the contents of one of the tables. The tables are all related by Student ID

The source table has the student marks as multiple rows per student, the table I need has the same information in a single row for each student.

The source table is:

Student ID	Student name	Subject	Term	Result
s1	Mary	English	Autumn	d
s1	Mary	English	Spring	s
s1	Mary	Maths	Autumn	d
s1	Mary	Maths	Spring	s
s2	John	English	Autumn	s
s2	John	English	Spring	s
s2	John	Maths	Autumn	s
s2	John	Maths	Spring	m

The table I need is

Student ID	Student name	English Autumn	English Spring	Maths Aumn	Maths Spring
s1	Mary	d	s	d	s
s2	John	s	s	s	m

The requirement is then to analyse by grades for different groups of students (eg those who are below an absence threshold), which I can do with the second table but not the first.

So my question is, is what I want to acheive possible with DAX?

Thanks in advance - and apologies if the question has already been asked!

B

[v-frfei-msft · Accepted solution]

Hi @barbaratate ,

Based on my test, that could not be achieved by dax. I suggest to use power query to have a try.

[barbaratate]

Thank you.

Thank you - that's the problem with not knowing what you can do - it's not knowing what you CAN'T do as well!

Barbara