cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Liwi Frequent Visitor
Frequent Visitor

get start of X event which finished first and apply to the event starting next

Hi All,
I am hoping someone can help me with DAX for below problem
I need to get start of the event which finished the earliest, in the row corresponding to the event starting next.

Example below

Event C finishing first therefore start of event C is applied to event starting next which is event F

Event A finishing as second and the start of A event is applied to the event G which is starting next after C event.

 

I don't even know where to start with it 

 

Appreciate any advice

Thank you in advance 

 Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: get start of X event which finished first and apply to the event starting next

Hi @Liwi,

 

Please refer to below DAX formula:

Index =
IF (
    Event[Start/End] = "Start",
    IF (
        Event[Datetime]
            <= MINX ( FILTER ( Event, Event[Start/End] = "End" ), Event[Datetime] ),
        BLANK (),
        RANKX (
            FILTER (
                Event,
                Event[Start/End] = "Start"
                    && Event[Datetime]
                        >= MINX ( FILTER ( Event, Event[Start/End] = "End" ), Event[Datetime] )
            ),
            Event[Datetime],
            ,
            ASC,
            DENSE
        )
    ),
    RANKX (
        FILTER ( Event, Event[Start/End] = "End" ),
        Event[Datetime],
        ,
        ASC,
        DENSE
    )
)

New Start =
IF (
    Event[Start/End] = "Start",
    LOOKUPVALUE (
        Event[Datetime],
        Event[Index], Event[Index],
        Event[Start/End], "End"
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft Super Contributor
Super Contributor

Re: get start of X event which finished first and apply to the event starting next

Hi @Liwi,

 

Please refer to below DAX formula:

Index =
IF (
    Event[Start/End] = "Start",
    IF (
        Event[Datetime]
            <= MINX ( FILTER ( Event, Event[Start/End] = "End" ), Event[Datetime] ),
        BLANK (),
        RANKX (
            FILTER (
                Event,
                Event[Start/End] = "Start"
                    && Event[Datetime]
                        >= MINX ( FILTER ( Event, Event[Start/End] = "End" ), Event[Datetime] )
            ),
            Event[Datetime],
            ,
            ASC,
            DENSE
        )
    ),
    RANKX (
        FILTER ( Event, Event[Start/End] = "End" ),
        Event[Datetime],
        ,
        ASC,
        DENSE
    )
)

New Start =
IF (
    Event[Start/End] = "Start",
    LOOKUPVALUE (
        Event[Datetime],
        Event[Index], Event[Index],
        Event[Start/End], "End"
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Liwi Frequent Visitor
Frequent Visitor

Re: get start of X event which finished first and apply to the event starting next

Oh wow.

Thank you so much Yuliana 

 

May I ask how did you get the index to appear this way?

 

 

v-yulgu-msft Super Contributor
Super Contributor

Re: get start of X event which finished first and apply to the event starting next

Hi @Liwi,

 

Please see above DAX formula for [Index].

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Liwi Frequent Visitor
Frequent Visitor

Re: get start of X event which finished first and apply to the event starting next

sorry, missed that 😉

Assumed you did it in query editor.

 

Thanks lot for your help 

Liwi Frequent Visitor
Frequent Visitor

Re: get start of X event which finished first and apply to the event starting next

Hello @v-yulgu-msft,

May I pick your brains again?

I have noticed that in situation where I have some event END between START of other ones it doesn't give me correct results as it is looking at MIN End from whole day
Is it possible for index of START event to start count all over after MAX of END which is < then current row?

 

Probably picture is best way to explain what I mean.Capture.PNG

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 299 members 3,083 guests