Forum Discussion

Asim's avatar
Asim
Helper I
8 years ago
Solved

Count with specific condition

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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