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 )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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

Zubair_Muhammad,

This measure is working perfect without any negative impact on speed or RAM.

 

thank you very much for your help

 

regards

Asim