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_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 c...
  • 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.

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi Carlend 

 

Can you add more details about your final result?

 

Appreciate your Kudos!!

 

Carlend's avatar
Carlend
Frequent Visitor
4 years ago

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

 

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

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

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

      • VahidDM's avatar
        VahidDM
        Icon for Super User rankSuper User
        4 years ago

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