Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating column values based on date match between tables

I'm attempting to perform a column calculation that will return a value based on the last date of an instrument calibration prior to a quality control check. I have two tables, Calibration and QC, wh...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi Anonymous ,

     

    here are two calc Columns that I think do the trick

     

     

    last Cal Xq val = 
var serial = [Serial]
var calDate = [QC Date]
var maxDate = CALCULATE(MAX('Calibration'[Cal Date]), FILTER(Calibration, Calibration[Serial] = 'QC'[Serial] && Calibration[Cal Date] <= calDate ))
return 
CALCULATE(MAX('Calibration'[Cal Xq]), FILTER('Calibration', Calibration[Serial] = serial && Calibration[Cal Date] = maxDate))

     

     

     

    var = DIVIDE([QC Xq], [last Cal Xq val])-1

     

     

     

    Hope this Helps


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

jdbuchanan71 

 

That's definitely a promining start, thanks! I'm getting the error "A single value for column 'Serial' in table 'QC' cannot be determined." due to there being multiple entries in the table for each serial number. Using a MIN or MAX function yields incorrect results, as I think it's picking values associated with different dates in that case. Is there a way to specify that the variable only uses the serial number, date, and Xq from that row?

 

I appreciate the help!

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Anonymous 

You are adding it as a calculated column on the QC table yes?  The VAR will read the current row of the table it is working on.