Subtracting Rows ( subtracting Rows Values from Same column based on value from another table)

[shreyansh008]

Hello Experts,

I am trying to create calucated column using DAX Function where i am trying to subtract different subject score based on User ID. 

I have three table, Table1 contain candidate User ID information, Table2 contain Score and Subject ID,and Table3 contain Subject name and Subject ID. Table structure are below :

Table1
ID1	User ID
1	A
2	B
3	C
4	D

Table2
ID1	Subject ID	Score
1	A1	23
2	A2	34
2	A1	21
1	A2	43
1	A3	32

Table3
Subject ID	Subject
A2	English
A1	Maths
A3	Science
A4	History

I am trying to create DAX Column below :

	A	B	DAX Column
Maths	23	21	SQRT(23-21)
English	43	34	SQRT(43-34)

How can i create Calculated column (DAX Column)? Please Help

Thanks

[dax · Accepted solution]

Hi @shreyansh008 , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

I am not sure about the calculation you are trying to do and how you plan to visualize it.  Assuming you have a 1:Many between Table 3 and Table 2, you could calculate the square root of the difference between the max and min score for each subject with this DAX expression:

SqrtMaxMin = 

var maxscore = calculate(max(Table2[Score])

var minscore = calculate(min(Table2[Score])

var difference = maxscore-minscore

return sqrt(difference)

If that is not right, maybe you can adapt that to meet your needs.  Note the the Calculate() is key to create context transition (i.e., use the Subject on Table 3 as a filter for the calculation on Table 2).

If this works for you, please mark it as the solution.  Kudos are great too.  Please let me know if it doesn't or if any questions.
Regards,
Pat