Calculated Column Issue - Single Value

[AvPowerBI]

Hi,

I am trying to write a calculated column based on the following logic:

IF Description from the Type Table is equal to "First Day" or "Second Day"  THEN how many Minutes between StartDateTime and EndDateTime from the Call Fact table,

IF Minutes between StartDateTime and EndDateTime is greater than 480 minutes then set this to 45 minutes 

ELSE set this to number of minutes between StartDateTime and EndDateTime

I thencreated a Calculated Column with the below DAX forumula : 

TimeTaken =

IF('Type'[Description] IN {"First Day","Second Day"},
DATEDIFF('Call Fact'[StartDateTime], 'Call Fact'[EndDateTime], MINUTE),
IF(DATEDIFF('Call Fact'[StartDateTime], 'Call Fact'[EndDateTime], MINUTE) > 480, 45,
DATEDIFF('Call Fact'[StartDateTime], 'Call Analysis'[EndDateTime], MINUTE)))

But I get the following error message:

A single value for column 'Description' in table 'Type' 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 ideas?

[v-jayw-msft · Accepted solution]

Hi @AvPowerBI ,

You will need to define StartDateTime and EndDateTime in the Type table first.

Please check the following formula.

Column = 
var _start = LOOKUPVALUE('Call Fact'[StartDateTime],'Call Fact'[id],'Type'[id])
var _end = LOOKUPVALUE('Call Fact'[EndDateTime],'Call Fact'[id],'Type'[id])
var _diff = IF('Type'[Description] IN {"First Day","Second Day"},DATEDIFF(_start,_end,MINUTE))
return
IF(_diff>480,45,_diff)

Best Regards,

Jay

[amitchandak]

@AvPowerBI , You can not use second table like that. Depending on relation

IF(related('Type'[Description]) IN {"First Day","Second Day"},
DATEDIFF('Call Fact'[StartDateTime], 'Call Fact'[EndDateTime], MINUTE),
IF(DATEDIFF('Call Fact'[StartDateTime], 'Call Fact'[EndDateTime], MINUTE) > 480, 45,
DATEDIFF('Call Fact'[StartDateTime], 'Call Analysis'[EndDateTime], MINUTE)))

or

IF(maxx(relatedtable('Type'),[Description]) IN {"First Day","Second Day"},
DATEDIFF('Call Fact'[StartDateTime], 'Call Fact'[EndDateTime], MINUTE),
IF(DATEDIFF('Call Fact'[StartDateTime], 'Call Fact'[EndDateTime], MINUTE) > 480, 45,
DATEDIFF('Call Fact'[StartDateTime], 'Call Analysis'[EndDateTime], MINUTE)))

Also, refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8