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: SN Date Status 111...
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.

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?