topic Re: Filter based on a value from a previous row in DAX Commands and Tips

Filter based on a value from a previous row

Carlend — Mon, 11 Oct 2021 09:36:18 GMT

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

Re: Filter based on a value from a previous row

Tanushree_Kapse — Mon, 11 Oct 2021 12:32:57 GMT

Hi Carlend ,

Do the following changes for the column N_Cicle

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

Re: Filter based on a value from a previous row

VahidDM — Mon, 11 Oct 2021 12:34:00 GMT

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!!

Re: Filter based on a value from a previous row

Carlend — Mon, 11 Oct 2021 12:46:52 GMT

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

Re: Filter based on a value from a previous row

Carlend — Mon, 11 Oct 2021 12:47:56 GMT

Re: Filter based on a value from a previous row

VahidDM — Mon, 11 Oct 2021 12:50:30 GMT

Hi Carlend

Can you add more details about your final result?

Appreciate your Kudos!!

Re: Filter based on a value from a previous row

Carlend — Mon, 11 Oct 2021 12:55:01 GMT

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

Re: Filter based on a value from a previous row

VahidDM — Mon, 11 Oct 2021 12:59:39 GMT

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!!

Re: Filter based on a value from a previous row

Carlend — Mon, 11 Oct 2021 13:26:36 GMT

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

Re: Filter based on a value from a previous row

VahidDM — Mon, 11 Oct 2021 21:57:33 GMT

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!!

Re: Filter based on a value from a previous row

Icey — Wed, 20 Oct 2021 10:10:07 GMT

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.