Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Carlend
Frequent Visitor

Filter based on a value from a previous row

Hi, i have a scenario like: 

Recipe_NameN_Cicle
Test12
Test12
Test11
Test10
Test23
Test23
Test22
Test21
Test20

 

And i want to filter N_Cicle column to obtain: 

Recipe_NameN_Cicle
Test12
Test11
Test10
Test23
Test22
Test21
Test20

 

Witch dax formula can i use?
Thanks so much

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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 )

Icey_0-1634724530323.png

 

 

Best Regards,

Icey

 

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

View solution in original post

10 REPLIES 10
Icey
Community Support
Community Support

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 )

Icey_0-1634724530323.png

 

 

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
Super User
Super User

Hi @Carlend 

 

try to add a new table with this code:

 

 

Table 2 = SUMMARIZE('Table','Table'[Recipe_Name],'Table'[N_Cicle])
 
output:
VahidDM_0-1633955614250.png

 

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

Appreciate your Kudos!!

 

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

Hi @Carlend 

 

Can you add more details about your final result?

 

Appreciate your Kudos!!

 

Sure, so.. i have:

Hi, i have a scenario like: 

Recipe_NameN_CicleMixTime
Test12157
Test12189
Test11132
Test10140
Test23145
Test23158
Test22135
Test21128
Test20133

 

And i want to filter N_Cicle column to obtain: 

Recipe_NameN_CicleMixTime
Test12157
Test11132
Test10140
Test23145
Test22135
Test21128
Test20133

 

Try this:

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

 

Output:

 

VahidDM_0-1633957170848.png

 

 

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

Appreciate your Kudos!!

 

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

@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:

VahidDM_0-1633989440145.png

 

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
Impactful Individual
Impactful Individual

Hi @Carlend ,

 

Do the following changes for the column N_Cicle

 

Tanushree_Kapse_0-1633955505772.png

 

 

 

Tanushree_Kapse_1-1633955545911.png

 

 

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

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.