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)

     

     

     

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)