Filter based on a value from a previous row

[Carlend]

Hi, i have a scenario like: 

Recipe_Name	N_Cicle
Test1	2
Test1	2
Test1	1
Test1	0
Test2	3
Test2	3
Test2	2
Test2	1
Test2	0

And i want to filter N_Cicle column to obtain: 

Recipe_Name	N_Cicle
Test1	2
Test1	1
Test1	0
Test2	3
Test2	2
Test2	1
Test2	0

Witch dax formula can i use?
Thanks so much

[Icey · Accepted solution]

Hi @Carlend ,

Can this meet your requirements?

Measure =
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[N_Cicle] ),
        ALLEXCEPT ( 'Table', 'Table'[Recipe_Name], 'Table'[N_Cicle] )
    )
VAR FirstIndex_ =
    IF (
        Count_ > 1,
        CALCULATE (
            MIN ( 'Table'[Index] ),
            ALLEXCEPT ( 'Table', 'Table'[Recipe_Name], 'Table'[N_Cicle] )
        ),
        MIN ( 'Table'[Index] )
    )
RETURN
    IF ( MIN ( 'Table'[Index] ) = FirstIndex_, 1 )

Best Regards,

Icey

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

[VahidDM]

Hi @Carlend 

try to add a new table with this code:

Table 2 = SUMMARIZE('Table','Table'[Recipe_Name],'Table'[N_Cicle])

output:

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

Appreciate your Kudos!!

[Carlend]

Hi, thanks for the help but this solution doesn't work because there is actually a third column with "different" values. I need to take only one of the rows creating a calculated column that allows me to say for example: if the value of N_Cicle is equal to the previous value of N_Cicle, return me one of the two. or something like that

[VahidDM]

Hi @Carlend 

Can you add more details about your final result?

Appreciate your Kudos!!

[Carlend]

Sure, so.. i have:

Hi, i have a scenario like: 

Recipe_Name	N_Cicle	MixTime
Test1	2	157
Test1	2	189
Test1	1	132
Test1	0	140
Test2	3	145
Test2	3	158
Test2	2	135
Test2	1	128
Test2	0	133

And i want to filter N_Cicle column to obtain: 

Recipe_Name	N_Cicle	MixTime
Test1	2	157
Test1	1	132
Test1	0	140
Test2	3	145
Test2	2	135
Test2	1	128
Test2	0	133

[VahidDM]

Try this:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Recipe_Name],
    'Table'[N_Cicle],
    "MixTime", MIN ( 'Table'[MixTime] )
)

Output:

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

Appreciate your Kudos!!

[Carlend]

It's not possibile to filter the same table without creating a new one?

[VahidDM]

@Carlend 

It's possible, add a new column with this code:

New Column = 
VAR _M =
    CALCULATE (
        MIN ( 'Table'[MixTime] ),
        FILTER (
            'Table',
            'Table'[Recipe_Name] = EARLIER ( 'Table'[Recipe_Name] )
                && 'Table'[N_Cicle] = EARLIER ( 'Table'[N_Cicle] )
        )
    )
RETURN
    IF ( [MixTime] > _M, 0, 1 )

then filter that column to show 1.

Output:

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

Appreciate your Kudos!!

[Tanushree_Kapse]

Hi @Carlend ,

Do the following changes for the column N_Cicle

Mark this as a solution, if I answered your question. Thanks

[Carlend]

Hi, thanks for the help but this solution doesn't work because there is actually a third column with "different" values. I need to take only one of the rows creating a calculated column that allows me to say for example: if the value of N_Cicle is equal to the previous value of N_Cicle, return me one of the two. or something like that