Forum Discussion

andrew_hardwick's avatar
andrew_hardwick
Helper III
9 years ago
Solved

A single value for column 'Open_Date_Time__c' in table 'Problem' cannot be determined

I am trying to create a column to create the difference between 2 dates in seperate tables. The first table holds the records that were created in Table 1, the second table holds when the fields in t...
Need Help
  • andrew_hardwick's avatar
    andrew_hardwick
    9 years ago

    Sorry for the dlay in repsonding. TBH I dont care whether I use a Column or measure, as long as it calculates for me :-)

     

    I have changed this to a measure, with DAX like below and this now works. Thanks for your help!

     

    MeasureDaysToNextValue = if(min('history: problem'[createddate])>0,CALCULATE(sum('Date'[IsWorkday]),DATESBETWEEN('Date'[Date],min(Problem[Open_Date_Time__c]),MIN('History: Problem'[CreatedDate]))),0)

     

     

     

malagari's avatar
malagari
Continued Contributor
9 years ago

Are you creating a measure, or a custom column? The behavior you're seeing makes me think that the MIN() function is taking the minimum of the entire date column, rather than for the specific RecordID.

andrew_hardwick's avatar
andrew_hardwick
Helper III
9 years ago

Yes I am trying to create a custom column. I agree it does seem to be taking the minimum of the column and not the specific record id. It seems as if the relationship is not being acknowledged.

  • malagari's avatar
    malagari
    Continued Contributor
    9 years ago

    Using the MIN() function in a custom column takes the minimum of the column for the entire table.  You can either create this as a Measure, which will take the row context that it's applied to into consideration, and only grab the MIN() for that particular row (and applied relationship filters).

    If you're set on having a custom column, you'll want to use the EARLIER() function to make sure you're comparing records of the same ID.  It'd look something like:

    CALCULATE( DATEDIFF(StartDate, EndDate, DAY), FILTER(Table, RecordId = EARLIER(RecordId) )
    • andrew_hardwick's avatar
      andrew_hardwick
      Helper III
      9 years ago

      Sorry for the dlay in repsonding. TBH I dont care whether I use a Column or measure, as long as it calculates for me :-)

       

      I have changed this to a measure, with DAX like below and this now works. Thanks for your help!

       

      MeasureDaysToNextValue = if(min('history: problem'[createddate])>0,CALCULATE(sum('Date'[IsWorkday]),DATESBETWEEN('Date'[Date],min(Problem[Open_Date_Time__c]),MIN('History: Problem'[CreatedDate]))),0)