Forum Discussion

Noredlac86's avatar
Noredlac86
Icon for Helper I rankHelper I
3 years ago
Solved

Count if consecutive values DAX

Hi Team.

I was wondering if you can help me with this question


I have two 3 columns, i need to count how many time show "Yes" in each ID in the consecutive order months:

MonthNo    ID   Value
12                001   Yes
11                001   Yes
10                001   Yes
9                  001   Yes
12                002   Yes
10                002   Yes
12                003   Yes
11                003  No

Results:
ID 001= 4 
ID 002= 2
ID 003 =0


The column Month wont have all months,  for example the ID002, only have month 12 and 10, thise month are consecutive cause mont 11 does not show, does mean that if we have 1,3,5,10. This is also consecutive

What formula can i use?

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Noredlac86 ,

    The Value column is the column with the data "Yes" or "No". 

    From your error message, I guess you use it as a measure. You can download my sample below to see the details.

     

    Best Regards,
    Community Support Team _ kalyj

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

11 Replies

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    3 years ago

    Noredlac86 here's my attempt at it. Not sure if this is what you're going for but let's give it a try:

     

    Flag Consecutive = 

VAR _ID = [ID]
VAR _Mval = [MonthNo]

VAR _NextMonth = CALCULATE( MIN(YOUR_TABLE[MonthNo]), FILTER(YOUR_TABLE, YOUR_TABLE[ID] = EARLIER(YOUR_TABLE[ID]) && YOUR_TABLE[MonthNo] > _Mval ))         // Find next month
VAR _NextValue = LOOKUPVALUE(YOUR_TABLE[Value], [ID], _ID, [MonthNo], _NextMonth)                                                                           // Return next month value

VAR _PrevMonth = CALCULATE( MAX(YOUR_TABLE[MonthNo]), FILTER(YOUR_TABLE, YOUR_TABLE[ID] = EARLIER(YOUR_TABLE[ID]) && YOUR_TABLE[MonthNo] < _Mval ))         // Find previous month
VAR _PrevValue = LOOKUPVALUE(YOUR_TABLE[Value], [ID], _ID, [MonthNo], _PrevMonth)                                                                           // return previous month value

RETURN
 
// some crazy logic switch. looking UP and DOWN to check if there is a consecutive pair.
SWITCH( TRUE(),
    [Value] = "Yes" && ISBLANK(_NextValue) && _PrevValue = "Yes", 1,
    [Value] = "Yes" && ISBLANK(_PrevValue) && _NextValue = "Yes", 1,
    [Value] = "Yes" && _NextValue = "Yes" && _PrevValue = "No", 1,
    [Value] = "Yes" && _PrevValue = "Yes", 1
)

     

     

    • Noredlac86's avatar
      Noredlac86
      Icon for Helper I rankHelper I
      3 years ago

      Hi hnguy71 
      Thanks for your time to reply my message.

      I was checking and for your example, i need that sumarize the consecutive.

      For example ID 001 should show number= 4. Cause they have 4 consecutive values in Yes.

      How can change your code to show this data?

      • hnguy71's avatar
        hnguy71
        Icon for Super User rankSuper User
        3 years ago

        Hi Noredlac86 ,

         

        You don't change the code but rather create a measure:

        Total Flagged = SUM(YOUR_TABLE[Flag Consecutive])

         

        It'll add up all the 1s and you'll get your total value from there.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Noredlac86 ,

    According to your description, here's my solution.

    1.Add an index column in Power Query.

    2.Create a calculated column.

    Column =
VAR _Pre =
    MAXX (
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) - 1
        ),
        'Table'[Value]
    )
VAR _Next =
    MAXX (
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        ),
        'Table'[Value]
    )
RETURN
    IF ( [Value] = "Yes" && OR ( _Pre = "Yes", _Next = "Yes" ), 1 )

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

    • Noredlac86's avatar
      Noredlac86
      Icon for Helper I rankHelper I
      3 years ago

      Hi v-yanjiang-msft 

      Thanks for your time and reply. I was checking the formula and i think that "Value" column, is the column with the data "Yes" or "No"
      I run the formula and i received:
      "DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values."

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Noredlac86 ,

        The Value column is the column with the data "Yes" or "No". 

        From your error message, I guess you use it as a measure. You can download my sample below to see the details.

         

        Best Regards,
        Community Support Team _ kalyj

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