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

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

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!