Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Find longest continuous run DAX

Hi,

 

Consider following example:

 

PartWeekNumber
A1
A5
A4
A3
A11
A10
A15
A14
A16

 

There are 4 separate runs for part A which are (1), (3,4,5), (10,11), (14,15,16).

I need to have a measure which would output count of elements in the longest run, so in the example above this would be 3.

It does not matter if there is more than one equally long run, just need to display the highest number.

WeekNumber is not always sorted.

 

Thanks

Evan

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    Try the following calculated columns and measure:

     

    Column = 
IF(
    'Table'[WeekNumber] = 1,
    0,
    'Table'[WeekNumber] - CALCULATE(
                            MAX('Table'[WeekNumber]),
                            FILTER('Table',
                                'Table'[Part] = EARLIER('Table'[Part])
                                && 'Table'[WeekNumber] < EARLIER('Table'[WeekNumber])
                            )
                        )
)
    GroupMin = 
CALCULATE(
    MAX('Table'[WeekNumber]),
    FILTER(
        'Table',
        'Table'[Column] <> 1
        &&'Table'[Part] = EARLIER('Table'[Part])
        && 'Table'[WeekNumber] <= EARLIER('Table'[WeekNumber])
    )
)
    Measure = 
MAXX(
    'Table',
    CALCULATE(
        DISTINCTCOUNT('Table'[WeekNumber]),
        ALLEXCEPT(
            'Table',
            'Table'[Part],
            'Table'[GroupMin]
        )
    )
)

        

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Try the following calculated columns and measure:

     

    Column = 
IF(
    'Table'[WeekNumber] = 1,
    0,
    'Table'[WeekNumber] - CALCULATE(
                            MAX('Table'[WeekNumber]),
                            FILTER('Table',
                                'Table'[Part] = EARLIER('Table'[Part])
                                && 'Table'[WeekNumber] < EARLIER('Table'[WeekNumber])
                            )
                        )
)
    GroupMin = 
CALCULATE(
    MAX('Table'[WeekNumber]),
    FILTER(
        'Table',
        'Table'[Column] <> 1
        &&'Table'[Part] = EARLIER('Table'[Part])
        && 'Table'[WeekNumber] <= EARLIER('Table'[WeekNumber])
    )
)
    Measure = 
MAXX(
    'Table',
    CALCULATE(
        DISTINCTCOUNT('Table'[WeekNumber]),
        ALLEXCEPT(
            'Table',
            'Table'[Part],
            'Table'[GroupMin]
        )
    )
)

        

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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