Forum Discussion
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 table 1 were changed. I want to work out the difference between when the record was created and when it was changed. I have setup a relationship between the 2 tables based on the unique ID.
I have created a visualisation in Power BI that holds the Record ID, Open Date and Changed Date. This works fine as it stands, however the missing piece would be the difference between the 2 dates. I have tried creating the following column-
DaysToNextValue = CALCULATE(sum('Date'[IsWorkday]),DATESBETWEEN('Date'[Date],Problem[Open_Date_Time__c],'History: Problem'[CreatedDate]))
However I am getting the error-
A single value for column Open_Date_Time__c' in table 'Problem' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Any suggestions? Thanks.
Andy
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)
8 Replies
You'll want to use the DATEDIFF() function instead of trying to sum the dates between.
Give it a gander on the MSDN reference site: https://msdn.microsoft.com/en-us/library/dn802538.aspx
Hi malagari. I tried using DATEDIFF() as below, but still get the same error.
DaysToNextValue = DATEDIFF(Problem[Open_Date_Time__c],'History: Problem'[CreatedDate],DAY)
Regards,
Andy
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?
