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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Liwi
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
v-yulgu-msft
Employee
Employee

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
Employee
Employee

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.

Oh wow.

Thank you so much Yuliana 

 

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

 

 

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.

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

 

sorry, missed that 😉

Assumed you did it in query editor.

 

Thanks lot for your help 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.