DAX calculated column or measure, and displaying in table

[Anonymous]

Hi,

I'm looking for help with creating a specific table based on my data.

My data contains various columns, shown below.

When collecting data we have many experiments, each experiment is given a unique number, in the table above the experiments are represented in the "EXP" column.

Each experiment delivers a unique result, in the table above the result is represented in the "RESULT DATA" column.

When evaluating the results of an experiment they are typically compared to another experiment. In the table above the comparing experiment is listed as "COMPARE EXP".

When reviewing the result data the column "EXP DIFFERENCE" would result in -17 (EXP 1 (51), - EXP 2 (68)).

Now the part I need help with, I would like to have the "EXP DIFFERENCE" polulated in the table for each experiment. Is the correct way to handle this with a calculated column or a measure, and can you provide an example DAX of either or both methods?

[TomMartens · Accepted solution]

Hey,

you can create a calculated column like so:

Diff = 
var thisResult = 'tablename'[Result Data]
var lookupExperiment = 'tablename'[Compare Exp]
var lookupResult = LOOKUPVALUE('tablename'[Result Data] , 'tablename'[Exp] , lookupExperiment )
return
thisResult - lookupResult

Hopefully this is providing what you are looking for.

Regards,

Tom

[Anonymous]

@TomMartens 

Thanks for the help!!

Exactly what I was looking for.