Find longest continuous run DAX

[Anonymous]

Hi,

Consider following example:

Part	WeekNumber
A	1
A	5
A	4
A	3
A	11
A	10

A	15
A	14
A	16

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

[v-kkf-msft · Accepted solution]

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.

[amitchandak]

@Anonymous , continuous streak problem

Refer if old solution can help

https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393

My attempt

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1290346