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

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
8 years ago
Asim

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

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

Community Champion

8 years ago

Asim

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
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
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

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
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
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

Forum Discussion

Count with specific conditions

Related content

Count orders with specific conditions

Count rows with condition

Counting nulls

Conditional Formatting on Count %RT

Conditional count