CALCULATE MINIMUM based on 2 categoires

[Anonymous]

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.

[harshnathani · Accepted solution]

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)

[Anonymous · Accepted solution]

[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
            )
    )

[Anonymous]

Thanks daxer.   Works perfectly.

[harshnathani · Accepted solution]

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)

[Anonymous]

Thank you, Harsh.  It works well.