How to group records based on values and index criteria

[Austinger]

Hello my mighty friends,

I'm working on a time event log analysis dashboard, the final part is to show an event, how long it lasts.

I simplified the idea into something like the below table shows.

The main challenge is, if it's continuous "YES" result for <Judgment> column, I want to group them together by returning the first Index number for this group. It's easy to achieve in Excel but really kills me when I try to do it in DAX. Spend 2 days and no progress at all.

Really appreciate any help or ideas.

Index	Judgment	Result Group Index
1
2
3
4
5	YES	5
6
7
8
9	YES	9
10	YES	9
11	YES	9
12
13
14
15	YES	15
16	YES	15
17
18
19
20

Index	Judgment	Result Group Index
1		=IF(B2="YES",IF(B1="",A2,C1),"")
2		=IF(B3="YES",IF(B2="",A3,C2),"")
3		=IF(B4="YES",IF(B3="",A4,C3),"")
4		=IF(B5="YES",IF(B4="",A5,C4),"")
5	YES	=IF(B6="YES",IF(B5="",A6,C5),"")
6		=IF(B7="YES",IF(B6="",A7,C6),"")
7		=IF(B8="YES",IF(B7="",A8,C7),"")
8		=IF(B9="YES",IF(B8="",A9,C8),"")
9	YES	=IF(B10="YES",IF(B9="",A10,C9),"")
10	YES	=IF(B11="YES",IF(B10="",A11,C10),"")
11	YES	=IF(B12="YES",IF(B11="",A12,C11),"")
12		=IF(B13="YES",IF(B12="",A13,C12),"")
13		=IF(B14="YES",IF(B13="",A14,C13),"")
14		=IF(B15="YES",IF(B14="",A15,C14),"")
15	YES	=IF(B16="YES",IF(B15="",A16,C15),"")
16	YES	=IF(B17="YES",IF(B16="",A17,C16),"")
17		=IF(B18="YES",IF(B17="",A18,C17),"")
18		=IF(B19="YES",IF(B18="",A19,C18),"")
19		=IF(B20="YES",IF(B19="",A20,C19),"")
20		=IF(B21="YES",IF(B20="",A21,C20),"")

[v-jingzhang · Accepted solution]

Hi @Austinger 

You can create a new column with this DAX

Column =
VAR vLastBlankRow =
    MAXX (
        FILTER (
            'Table',
            'Table'[Index] < EARLIER ( 'Table'[Index] )
                && 'Table'[Judgment] = ""
        ),
        'Table'[Index]
    )
RETURN
    IF ( 'Table'[Judgment] = "YES", vLastBlankRow + 1, BLANK () )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Austinger]

Hi Jing

That's a smart solution, I tried and works fine with my test file.

However, when I migrate all the event files in, the volume of data (around 1.2 million records) killed the machine, my computer ran for 6 hours and no result.

But thanks anyway, really appreciate your help.