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!

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

 

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    6 years ago

    Hi Anonymous,

     

    What is the difference between the RECORDS_AD and RECORDS_RP Tables? Is RECORDS_RP[Xq] a calculation that references RECORDS_AD[Xq]?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Apologies, I should have made the language consistent with the example above - RECORDS_AD is essentially the Calibration table ("AD" for calibration "adjustment"), and RECORDS_RP is the QC table (RP for "routine performance" check); so my intent is to calculate the difference between each RECORDS_RP[Xq] and the corresponding RECORDS_AD[Xq] from the last calibration adjustment date. 

       

      Sorry for the confusion!

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        6 years ago

        Hi Anonymous,

         

        Can you provide a sample pbix with the error?