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

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

Dear Zubair_Muhammad,

After putting new formula in measure i am getting below result which is not as per desire result.

In fact Calculated column is giving precise result but the problem is that its occupy huge memory which create problem when the data grow.

 

Please help!

 

Regards

Asim

 

 

 

 

 

  • 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