Dax question about comparing non-adjacent row values in same column

[someuser999]

Comparing row values. In Dax, for each subject and test item, how to determine the difference (numeric) between the type 3 result and the type 1 result?

Subject Tester TestType TestItem1 TestItem2 TestItem3
Subject 1 Tester 1 Type 1 Result 1 Result 1 Result 1
Subject 1 Tester 1 Type 2 Result 2 Result 2 Result 2
Subject 1 Tester 2 Type 3 Result 3 Result 3 Result 3
Subject 2 Tester 1 Type 1 Result 1 Result 1 Result 1
Subject 2 Tester 3 Type 3 Result 2 Result 2 Result 2

[TomMartens · Accepted solution]

Hey,
wondering if you considered to use
LOOKUPVALUE 

https://msdn.microsoft.com/en-us/query-bi/dax/lookupvalue-function-dax

in combination with unpivoting the columns TestItem1 ... TestItem3

If this is not what you are looking for, please consider to create a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link.

Regards,

Tom

[someuser999]

Looks like that could work. Could it be done without hard coding all the search column values?

[TomMartens]

Hey,

basically you can use variables that reduces hardcoding dramtically, also consider to pivot the columns Testresult1 - Testresult3 into one column

Everything gets much more simple if you provid a pbix with sample data, upload the file to onedrive or dropbox and share the link.

Regards

Tom