Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering a table with date dependency

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:

SNDateStatus
11108.01.2022 06:55failed
11108.01.2022 07:00passed
22208.01.2022 07:02passed
33308.01.2022 07:03failed
33308.01.2022 07:04failed
33308.01.2022 07:05passed
44408.01.2022 07:10failed
44408.01.2022 07:12failed
55508.01.2022 07:15passed
66608.01.2022 07:20passed

 

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

 

SNDateStatus
22208.01.2022 07:02passed
55508.01.2022 07:15passed
66608.01.2022 07:20passed

 

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

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.