Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
[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
)
)
[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
)
)
Thanks daxer. Works perfectly.
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)
Thank you, Harsh. It works well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |