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!

richbenmintz's avatar
richbenmintz
Resident Rockstar
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

    Hi richbenmintz,

    Apologies for the slow reply! I had been pulled into other projects and this one got backburnered. 

     

    I'm using your two-column solution which looks promising, but getting a circular dependency error for the second column when I attempt the DIVIDE function. 

     

    Using the information found at https://community.powerbi.com/t5/Desktop/Row-Identifier-within-Power-BI-for-Desktop-Circular-dependencies/td-p/6381 I've been trying to use ALLEXCEPT in the CALCULATE function to fix the error, but haven't had much luck.

     

    Thanks again for your help!

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      6 years ago

      can you share your pbix

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      6 years ago

      Hi Anonymous,

       

      Can you share your Calculated Column formula that is generating the circular dependency?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        The formulas I'm using are essentially what you had suggested above; so column 1 returns the last calibration Xq as intended:

         

         

        Last Cal Xq = 
VAR _SERIAL = [SerialNumber]
VAR _RPCDATE = [TestDate]
VAR _MAXDATE = 
    CALCULATE(
        MAX(RECORDS_AD[Cal Date]), 
        FILTER(RECORDS_AD, RECORDS_AD[SerialNumber] = _SERIAL && RECORDS_AD[Cal Date] <= _RPCDATE)
        )
RETURN
    CALCULATE(
        MAX(RECORDS_AD[Xq]), 
        FILTER(RECORDS_AD, RECORDS_AD[SerialNumber] = _SERIAL && RECORDS_AD[Cal Date] = _MAXDATE)
        )

         

         

         

        But the second column I'm calling "Off" gives the circular dependency error:

         

         

        Off = 
DIVIDE(RECORDS_RP[Xq], RECORDS_RP[Last Cal Xq])-1