Filtering a table with date dependency

[Anonymous]

Hello, I am relatively new to Power BI and have already done some research on my problem. However, I have not yet found a solution that works.
I have the following table:

SN	Date	Status
111	08.01.2022 06:55	failed
111	08.01.2022 07:00	passed
222	08.01.2022 07:02	passed
333	08.01.2022 07:03	failed
333	08.01.2022 07:04	failed
333	08.01.2022 07:05	passed
444	08.01.2022 07:10	failed
444	08.01.2022 07:12	failed
555	08.01.2022 07:15	passed
666	08.01.2022 07:20	passed

Now I have the following requirements:
1) Filter and display a new table, which looks like this:

SN	Date	Status
222	08.01.2022 07:02	passed
555	08.01.2022 07:15	passed
666	08.01.2022 07:20	passed

Only the SNs that have the status passed at the first attempt should be displayed. I have tried it over different nested functions, without success. Getting the date into the right dependency is difficult for me.

2) Count the rows in the filtered table (probably as measure with "CountRows"). The Result should be = 3 in my example. Should be simple:-)

Thanks already for your support

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution. I have two methods.

Method 1

1.Create a new table.

Table 2 = 
FILTER (
    ALL ( 'Table' ),
    MINX (
        FILTER ( ALL ( 'Table' ), 'Table'[SN] = EARLIER ( 'Table'[SN] ) ),
        'Table'[Status]
    ) = "passed"
)

Get the expected table.

2.Create a measure.

Count' = COUNTROWS('Table 2')

Get the correct count.

Method 2

1. Create a measure.

Check =
IF (
    MINX (
        FILTER ( ALL ( 'Table' ), 'Table'[SN] = MAX ( 'Table'[SN] ) ),
        'Table'[Status]
    ) = "passed",
    1,
    0
)

Select the visual, put the check measure in the visual filter, then select the value is 1.

Get the expected result.

2.Create another measure.

Count = 
CALCULATE (
    COUNT ( 'Table'[SN] ),
    FILTER (
        ALL ( 'Table' ),
        MINX (
            FILTER ( ALL ( 'Table' ), 'Table'[SN] = EARLIER ( 'Table'[SN] ) ),
            'Table'[Status]
        ) = "passed"
    )
)

Get the correct count.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@Anonymous , Create a new column like this and use that as filter

new column =
var _min = minx(filter(Table, Table[SN] = earlier([SN])),[DAte])
return
if([Date] =_min && [Status] = "passed" ,1, 0 )

or try a measure like

new measure =
var _min = minx(filter(allselected(Table), Table[SN] = max([SN])),[DAte])
return
calculate(max(Table[Status]), filter(Table, Table[Date] =_max ))

[Anonymous]

Hi @amitchandak,

Thanks for your suggestion. The idea with the additional column is an interesting solution. But I mainly need a filtered table as a result and not a additional column. The measure should count the rows of the filtered table afterwards.
Do you have another idea for this?