Flag ONLY 1st occurance of nonblank cell for each ID

[Ram_DCT]

Hi,

I need to flag 1st occurance of a nonblank number in each of my work item id and below is the sample data.

Work Item Id	Hours	Completed
6692	8/1/2021
6692	8/2/2021
6692	8/3/2021	3
6692	8/4/2021	3
6692	8/5/2021	5
6692	8/6/2021	5
6692	8/7/2021	7
6692	8/8/2021	8
6692	8/9/2021	8
6692	8/10/2021	10
6692	8/11/2021	10
6691	8/5/2021	7
6691	8/6/2021	7
6691	8/7/2021	7
6691	8/8/2021	7
6691	8/9/2021	14
6691	8/10/2021	14
6691	8/11/2021	16
6691	8/12/2021	16
6691	8/13/2021	16

Expected Result with a calculated colum should be as follows:

Work Item Id	Hours	Completed
6692	8/1/2021		0
6692	8/2/2021		0
6692	8/3/2021	3	1
6692	8/4/2021	3	0
6692	8/5/2021	5	0
6692	8/6/2021	5	0
6692	8/7/2021	7	0
6692	8/8/2021	8	0
6692	8/9/2021	8	0
6692	8/10/2021	10	0
6692	8/11/2021	10	0
6691	8/5/2021	7	1
6691	8/6/2021	7	0
6691	8/7/2021	7	0
6691	8/8/2021	7	0
6691	8/9/2021	14	0
6691	8/10/2021	14	0
6691	8/11/2021	16	0
6691	8/12/2021	16	0
6691	8/13/2021	16	0

[VahidDM · Accepted solution]

Hi @Ram_DCT 

1- add Index column to your table in Power Query:

2- Use below code [DAX] to add a column to your table:

FLAG Column = 
VAR _first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Work Item Id] ),
            'Table'[Completed] > 0
        )
    )
RETURN
    IF ( _first = 'Table'[Index], 1, 0 )

Output:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos  !!

[ryan_mayu · Accepted solution]

@Ram_DCT 

based on your sample data, you can create a column directly.

Column = 
VAR _first=MINX(FILTER('Table','Table'[Work Item Id]=EARLIER('Table'[Work Item Id])&&NOT(ISBLANK('Table'[Completed]))),'Table'[Hours])
return if('Table'[Hours]=_first,1,0)

[VahidDM · Accepted solution]

Hi @Ram_DCT 

1- add Index column to your table in Power Query:

2- Use below code [DAX] to add a column to your table:

FLAG Column = 
VAR _first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Work Item Id] ),
            'Table'[Completed] > 0
        )
    )
RETURN
    IF ( _first = 'Table'[Index], 1, 0 )

Output:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos  !!