Forum Discussion
A single value for column 'Open_Date_Time__c' in table 'Problem' cannot be determined
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)
Ah, I didn't see that you are referencing two tables. This error happens when there isn't a proper relationship between the two tables - meaning when you reference Problem[Open_Date_Time__c], it's returning more than one value and doesn't know which one to use.
Without seeing your data model, you could default to the MAX or MIN Open_Date_Time__c, depending on which makes sense.
Is there a relationship between your 'History: Problem' and 'Problem' table? If so, what cardinality is the relationship - one-to-one, many-to-one, one-to-many, or many-to-many?
The tables are laid at as follows-
Problem
ID - unique reference to that record.
Opened Date - Date and time the record was created
Problem History
ID - unique reference to that record.
ParentID - Link to the Problem it is linked with, this is the same IS as the Problem ID above. There can be many history records for a single problem.
CreatedDate - the date and time that this history record was made, ie when the problem record was changed.
I have created a relationship between ParentID in Problem History and ID in Problem as a Many to One relationship.
I have tried to use the MIN function against the Problem Opened Date (which should not really be necessary as there is only one ID) and whilst this does return data the numbers returned do not make semse, as shown below.
Record ID Open Date Created Date DaysToNextValue
00001013 09/02/2017 14:07:26 09/02/2017 16:23:21 290
00001019 14/02/2017 12:40:16 14/02/2017 12:45:21 293
00001037 21/02/2017 10:37:18 22/02/2017 13:05:11 299
I would have expected results like 0,0,1 respectively.
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.
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.
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) )