Forum Discussion

Asim's avatar
Asim
Helper I
8 years ago
Solved

Count with specific conditions

Hello!

Good day,

I would like to know the count of my semi material number is material is getting change, if not then i want to have 0 in column or measure.

I am using below mentioned dax formula in my calculated column to evalute the results but it is not working for some unknown reason. 

Is there any one help me out from this situation.

 

Best Regards

Asim

 

 

Count = IF(
CALCULATE(
COUNTA(Eventlog[Machine_Material]),
FILTER(
Eventlog,
Eventlog[Date] > EARLIER(Eventlog[Date])
&& Eventlog[Semi.Mat.No.] <> EARLIER(Eventlog[Semi.Mat.No.])
)
) > 1,
0,
1
)

 

 

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Asim

     

    Try this calculated column

     

    Column =
VAR PreviousDate =
    CALCULATE (
        MAX ( EventLog[Date] ),
        FILTER (
            ALLEXCEPT ( EventLog, EventLog[Machine] ),
            EventLog[Event] = "setUp"
                && EventLog[Date] < EARLIER ( EventLog[Date] )
        )
    )
VAR PreviousMaterial =
    CALCULATE (
        FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ),
        FILTER (
            ALLEXCEPT ( EventLog, EventLog[Machine] ),
            EventLog[Event] = "setUp"
                && EventLog[Date] = PreviousDate
        )
    )
RETURN
    IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, 0, 1 )
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Asim

     

    Try this MEASURE... I am not sure if it will speed up things

     

    Setup Count Measure =
VAR mytable =
    ADDCOLUMNS (
        Eventlog,
        "Asim",
        VAR PreviousDate =
            CALCULATE (
                MAX ( EventLog[Date] ),
                FILTER (
                    ALLEXCEPT ( EventLog, EventLog[Machine] ),
                    EventLog[Event] = "setup"
                        && EventLog[Date] < EARLIER ( EventLog[Date] )
                )
            )
        VAR PreviousMaterial =
            CALCULATE (
                FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ),
                FILTER (
                    ALLEXCEPT ( EventLog, EventLog[Machine] ),
                    EventLog[Event] = "setup"
                        && EventLog[Date] = PreviousDate
                )
            )
        RETURN
            IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, BLANK (), 1 )
    )
RETURN
    COUNTX ( FILTER ( mytable, [Asim] = 1 ), 1 )

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Asim,

    Create the following columns in your table.

    index = CALCULATE(COUNT(Eventlog[Date]),ALL(Eventlog),FILTER(Eventlog,Eventlog[Date]<=EARLIER(Eventlog[Date])))
    Column = var previous=CALCULATE( FIRSTNONBLANK(Eventlog[Semi.Mat.No],1),FILTER(Eventlog,Eventlog[index]=EARLIER(Eventlog[index])-1)) return IF(Eventlog[Semi.Mat.No]=previous,0,1)



    Regards,
    Lydia

    • Asim's avatar
      Asim
      Helper I
      8 years ago

      Dear Lydia,

      Thanks for your reply,

      Actually the table is getting filtered by date by machine code and by event, in this case it was showing the records for machine E120N1 with event= setup and with date 15/1/2018.

      after applying your suggested columns, I am getting below mentioned result.

       

      I also attached one table without applying filter.

       

      Regards

      Asim

       

       

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Asim,

        Please help to share sample data of your original tables and post expected result here.

        Regards,
        Lydia