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

[andrew_hardwick]

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

[malagari]

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

[andrew_hardwick]

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

[malagari]

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?

[andrew_hardwick]

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.

[malagari]

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]

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]

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 · Accepted solution]

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)