Forum Discussion

Carlend's avatar
Carlend
Frequent Visitor
4 years ago
Solved

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

  • Icey's avatar
    Icey
    4 years ago

    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.

10 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    4 years ago

    Hi Carlend ,

     

    Do the following changes for the column N_Cicle

     

     

     

     

     

     

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

     

    • Carlend's avatar
      Carlend
      Frequent Visitor
      4 years ago

      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's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      Carlend
      Frequent Visitor
      4 years ago

      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's avatar
        VahidDM
        Super User
        4 years ago

        Hi Carlend 

         

        Can you add more details about your final result?

         

        Appreciate your Kudos!!

         

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    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.