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 ...
  • 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 )
Asim's avatar
Asim
Helper I
8 years ago

ultimatly count will be the desire result.

 

 

DateMachineEventDurationOrderSemi.Mat.No.Count
1/16/2018 1:55E120N1setup353543589IIC37TCX6Y1
1/16/2018 4:24E120N1setup263543589IIC37TCX6R1
1/16/2018 7:10E120N1setup83543589IIC37TCX6U1
1/16/2018 8:52E120N1setup1003543809IIC61YCX6K1
1/16/2018 14:09E120N1setup153543809IIC61YCX6K0
1/16/2018 18:17E120N1setup223543809IIC61YCX6K0
1/16/2018 20:33E120N1setup203543504IIC37T4X6K1
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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
    Community Champion
    8 years ago

    Asim

     

    • Asim's avatar
      Asim
      Helper I
      8 years ago

      Zubair_Muhammad

      Good Morning!

      Thank you very much dear, you made my life so easy, 

      This solution is perfect!

       

       

      Best Regards

      Asim 

    • Asim's avatar
      Asim
      Helper I
      8 years ago

      Zubair_Muhammad

      Dear, the calculated column which you suggested earlier is working perfect except consuming RAM, The solution using below mentioned measure you suggested is not returning the desire result.

      could you please check!

       

      Regards

      Asim

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Asim

         

        Give this a shot

         

        Setup Count =
VAR PreviousDate =
    CALCULATE (
        MAX ( EventLog[Date] ),
        FILTER (
            ALLEXCEPT ( EventLog, EventLog[Machine] ),
            EventLog[Event] = "setup"
                && EventLog[Date] < SELECTEDVALUE ( EventLog[Date] )
        )
    )
VAR PreviousMaterial =
    CALCULATE (
        FIRSTNONBLANK ( EventLog[Semi.Mat.No.], 1 ),
        FILTER (
            ALLEXCEPT ( EventLog, EventLog[Machine] ),
            EventLog[Event] = "setup"
                && EventLog[Date] = PreviousDate
        )
    )
RETURN
    SUMX (
        SUMMARIZE ( EventLog, EventLog[Machine], EventLog[Semi.Mat.No.] ),
        IF ( EventLog[Semi.Mat.No.] = PreviousMaterial, BLANK (), 1 )
    )