Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

CALCULATE MINIMUM based on 2 categoires

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:

 

IDTypeRequest IDTimestampTime to first reply (minutes)
554QuestionABC1238/1/2020 13:493
231ReplyABC1238/1/2020 13:52 
895ReplyABC1238/1/2020 13:53 
522QuestionDEF2348/2/2020 8:005
120ReplyDEF2348/2/2020 8:05 
978QuestionGHI3458/2/2020 8:071
840ReplyGHI3458/2/2020 8:11 
92ReplyGHI3458/2/2020 8:08 
520ReplyGHI3458/2/2020 8:09 

 

epalacio1_0-1597947121619.png

 

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.

 

 

2 ACCEPTED SOLUTIONS
harshnathani
Community Champion
Community Champion

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

 

 

1.jpg

 

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)

View solution in original post

Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

[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
Not applicable

Thanks daxer.   Works perfectly.

harshnathani
Community Champion
Community Champion

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

 

 

1.jpg

 

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
Not applicable

Thank you, Harsh.  It works well.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors