Check value against entire column and return value once it occurred first time

[chriser]

Hi, 

I need to look through entire column and as soon as "TheOrder" value is found in OrderNumber column, Result will always return Special from then on against this specific CustomerID, regardless if consecutive order numbers are Normal or TheOrder.

Example:

CustomerID    OrderNumber        Result

1                     Normal                   Basic

2                     Normal                   Basic

1                     TheOrder                Special

3                     Normal                   Basic

1                     Normal                   Special

2                     TheOrder                Special

3                     TheOrder                Special

1                     TheOrder                Special

1                     Normal                   Special

Thanks in advance!

[v-lid-msft · Accepted solution]

Hi @chriser ,

It is nearly impossible to make result return special only when the order number occur “the order” unless your data have an increment primary column or a date key than can indicate the sequence of the orderNmuber.

Result =
VAR i = [Customer ID]
VAR time = [Date Key]
VAR t =
    FILTER ( 'ExampleWithTime', 'ExampleWithTime'[Customer ID] = i )
VAR t_order =
    FILTER ( t, [OrderNumber] = "TheOrder" )
VAR firstTime =
    MINX ( t_order, [Date Key] )
VAR specialStatus =
    COUNTROWS ( t_order ) > 0
RETURN
IF ( specialStatus, IF ( time >= firstTime, "Special", "Basic" ), "Basic" )

Or if you do not have such a column, we can go to power query editor to create an index column.

After creating index column, we can create a calculated column similar to the previous one to meet your requirement.

Result =
VAR i = [Customer ID]
VAR index = [Index]
VAR t =
    FILTER ( 'Example', 'Example'[Customer ID] = i )
VAR t_order =
    FILTER ( t, [OrderNumber] = "TheOrder" )
VAR firstIndex =
    MINX ( t_order, [Index] )
VAR specialStatus =
    COUNTROWS ( t_order ) > 0
RETURN
    IF ( specialStatus, IF ( index >= firstIndex, "Special", "Basic" ), "Basic" )

BTW, pbix as attached.

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

[chriser]

Thank you @v-lid-msft !

I do have a date column in my table so your soultion works really well.