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

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