DAX formula for "first error" column

[Anonymous]

Hi @all,

I don't have so much experience in creating DAX formula and need your support for this.

I have a table as follows (shortened and simplified version):

Serial number	Date Time	Error Message	Status
1001	28.12.2022 11:28:03	Error 1	Failed
1002	28.12.2022 11:33:12	Error 4	Failed
1001	28.12.2022 11:44:33	Error 1	Failed
1003	28.12.2022 12:02:07	Error 5	Failed
1004	28.12.2022 11:28:03		Passed
1002	28.12.2022 12:36:11	Error 18	Passed
1001	28.12.2022 12:55:52		Passed
1003	28.12.2022 12:09:09		Passed

Here I want to add a column "First Error" by a DAX formula to get the following result:

Serial number	Date Time	Error Message	Status	First Error
1001	28.12.2022 11:28:03	Error 1	Failed	1
1002	28.12.2022 11:33:12	Error 4	Failed	1
1001	28.12.2022 11:44:33	Error 1	Failed	0
1003	28.12.2022 12:02:07	Error 5	Failed	1
1004	28.12.2022 11:28:03		Passed	0
1002	28.12.2022 12:36:11	Error 18	Passed	0
1001	28.12.2022 12:55:52		Passed	0
1003	28.12.2022 12:09:09		Passed	0

Requirement: I want the "First Error" column to contain a 1 when an error has occurred for a serial number for the first time. So the time should be evaluated by the column "Date Time". If there are several errors for a serial number (also the same error), there should be 0 in the column.

I would be happy to receive suggestions 🙂

Best regards

Nemo999

[Mahesh0016]

-

[Anonymous]

@Mahesh0016 

Thanks for your approach. But I still have the remaining issue I already mentioned below.

[amitchandak]

@Anonymous ,

a new column

=

var _min = minx(filter(Table, [Serial Number] = earlier( [Serial Number]) ),[Date time])

return

if( [Date Time] =_min , 1,0)

[Anonymous]

@amitchandak 

Thank for your rapid feedback!

I tried your approach with real data and figured out that most of my requirements are covered with only one exception:

In this case...

1004

28.12.2022 11:28:03

Passed

0

no error occures and the value for "First Error" shall be 0. Currently the formula returns 1.

It seems so that the pararmeter "Status" must also be considered. Do you agree? Any suggestions?

[Mahesh0016 · Accepted solution]

a new column =

VAR _min =

    MINX (

        FILTER (

            Error_table,

            Error_table[Serial number] = EARLIER ( Error_table[Serial number] )

        ),

        Error_table[Date Time]

    )

RETURN

   IF ( Error_table[Date Time] = _min && Error_table[Error  Message]<>BLANK(), 1, 0 )

@Anonymous  In addition to @amitchandak response, I have added an extra condition in the IF statement. Please try this one.