Please help me out total counts when a value comes out in consecutive order

[Rider03]

Dax for total counts when a value comes out in 3 consecutive order. Highly appreciated the support.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below measure and the attached pbix file.

Expected result measure: = 
VAR _newtable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Data,
            "@prevvalue",
                CALCULATE (
                    SUM ( Data[Values count] ),
                    FILTER ( Data, Data[Time] = EARLIER ( Data[Time] ) - 1 )
                )
        ),
        "@condition", IF ( Data[Values count] <> [@prevvalue], 1 )
    )
VAR _cumulatecondition =
    ADDCOLUMNS (
        _newtable,
        "@cumulatecondition",
            SUMX (
                FILTER ( _newtable, Data[Time] <= EARLIER ( Data[Time] ) ),
                [@condition]
            )
    )
VAR _filteronlyone =
    FILTER ( _cumulatecondition, Data[Values count] = 1 )
VAR _count =
    FILTER (
        GROUPBY (
            _filteronlyone,
            [@cumulatecondition],
            "@count", SUMX ( CURRENTGROUP (), 1 )
        ),
        [@count] >= 3
    )
RETURN
    COUNTROWS( _count )

[Rider03]

It worked. You are Einstien. Thank you very much.