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...
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

amitchandak's avatar
amitchandak
Super User
5 years ago

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