topic Re: CALCULATE MINIMUM based on 2 categoires in DAX Commands and Tips

CALCULATE MINIMUM based on 2 categoires

Anonymous — Thu, 20 Aug 2020 18:12:12 GMT

Hello,

Could use some help figuring out the DAX syntax for the following scenario.

I have a single table with a self referential hierarchy that looks like this:

ID	Type	Request ID	Timestamp	Time to first reply (minutes)
554	Question	ABC123	8/1/2020 13:49	3
231	Reply	ABC123	8/1/2020 13:52
895	Reply	ABC123	8/1/2020 13:53
522	Question	DEF234	8/2/2020 8:00	5
120	Reply	DEF234	8/2/2020 8:05
978	Question	GHI345	8/2/2020 8:07	1
840	Reply	GHI345	8/2/2020 8:11
92	Reply	GHI345	8/2/2020 8:08
520	Reply	GHI345	8/2/2020 8:09

I am trying to calculate the value in the "Time to First Reply" column, basically for each question, subtract the earliest reply timestamp from the question timestamp.

In pseudo code this would look something like this:

(RequestID(timestamp), Filter(type = "Question)) - (min(request_id(timestamp)), Filter(type = "Reply"))

I just can't get the FILTER, ALLEXCEPT etc.. to give me what i need.

Thanks for any help you can provide.

Re: CALCULATE MINIMUM based on 2 categoires

Anonymous — Thu, 20 Aug 2020 18:47:24 GMT

Hi Anonymous ,

Create a Column

Column = var a = CALCULATE(MIN('Table'[Timestamp]), FILTER('Table','Table'[Request ID] = EARLIER('Table'[Request ID]) && 'Table'[Type] = "Reply")) RETURN IF ('Table'[Type] = "Question" , DATEDIFF('Table'[Timestamp],a, MINUTE), BLANK())

Incase you need a measure

First Reply = var a = CALCULATE(MIN('Table'[Timestamp]), FILTER(ALL('Table'),'Table'[Request ID] = MAX('Table'[Request ID]) && 'Table'[Type] = "Reply")) RETURN IF (MAX('Table'[Type]) = "Question" , DATEDIFF(MAX('Table'[Timestamp]),a, MINUTE), BLANK())

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: CALCULATE MINIMUM based on 2 categoires

Anonymous — Fri, 21 Aug 2020 11:44:52 GMT

[TTFR (mins)] = // calc column var __type = T[Type] return if( __type = "question", var __request = T[Request ID] var __requestTime = T[Timestamp] var __replyTime = MINX( filter( T, T[Request ID] = __reqest && T[Type] = "reply" ), T[Timestamp] ) return DATEDIFF( __requestTime, __replyTime, MINUTE ) )

Re: CALCULATE MINIMUM based on 2 categoires

Anonymous — Mon, 24 Aug 2020 14:40:23 GMT

Thanks daxer. Works perfectly.

Re: CALCULATE MINIMUM based on 2 categoires

Anonymous — Mon, 24 Aug 2020 14:41:09 GMT

Thank you, Harsh. It works well.