Forum Discussion

AvPowerBI's avatar
AvPowerBI
Post Patron
5 years ago
Solved

Calculated Column Issue - Single Value

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?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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