Count with specific condition

[Asim]

Hi there,

Good day!

I have been facing problem while trying to calculate the count of semi material number.

I have been using below mentioned formula in a calculated column to calculate the count of semi material number but it is not giving me the desire result,

The logic is that if machine is under setup and previous row and current row have the semi material number then i want to have 1 otherwise 0 in my calculated column. 

Count = IF(
CALCULATE(
COUNTA(Eventlog[Machine_Material]),
FILTER(Eventlog,
Eventlog[Date] > EARLIER(Eventlog[Date])
&& (Eventlog[Machine_Material]) = EARLIER(Eventlog[Machine_Material])
)
)> 1,
0,
1
)

Is there someone help me out please.

Regards

Asim

[v-shex-msft · Accepted solution]

Hi @Asim,

I'd like to suggest you use below measure formula to instead.

Sample:

Count =
VAR filtered =
    COUNTROWS (
        FILTER (
            Eventlog,
            Eventlog[Date] > MAX ( Eventlog[Date] )
                && ( Eventlog[Machine_Material] ) = SELECTEDVALUE ( Eventlog[Machine_Material] )
        )
    )
RETURN
    IF ( filtered > 1, 0, 1 )

BTW, you can also use below formula if you had to use calculate column to achieve your requirement:

Count =
IF (
    CALCULATE (
        COUNTA ( Eventlog[Machine_Material] ),
        FILTER (
            ALL ( Eventlog ),
            Eventlog[Date] > EARLIER ( Eventlog[Date] )
                && ( Eventlog[Machine_Material] ) = EARLIER ( Eventlog[Machine_Material] )
        )
    )
        > 1,
    0,
    1
)

Regards,

Xiaoxin Sheng