Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
Asim
Helper I
Helper I

Count with specific conditions

‎02-06-2018 04:05 AM

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 in my calculated column to evalute the results but it is not working for some unknown reason. 

Is there any one help me out from this situation.

 

Best Regards

Asim

 

 

Count = IF(
CALCULATE(
COUNTA(Eventlog[Machine_Material]),
FILTER(
Eventlog,
Eventlog[Date] > EARLIER(Eventlog[Date])
&& Eventlog[Semi.Mat.No.] <> EARLIER(Eventlog[Semi.Mat.No.])
)
) > 1,
0,
1
)

 

 

 Count.png

 

Message 1 of 14
2,456 Views
0
3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-07-2018 03:58 AM

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

Regards
Zubair

Please try my custom visuals

View solution in original post

Message 7 of 14
2,529 Views
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-20-2018 11:15 PM

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

Message 13 of 14
2,483 Views
13 REPLIES 13
Anonymous
Not applicable

‎02-06-2018 10:22 PM

@Asim,

Create the following columns in your table.

index = CALCULATE(COUNT(Eventlog[Date]),ALL(Eventlog),FILTER(Eventlog,Eventlog[Date]<=EARLIER(Eventlog[Date])))
Column = var previous=CALCULATE( FIRSTNONBLANK(Eventlog[Semi.Mat.No],1),FILTER(Eventlog,Eventlog[index]=EARLIER(Eventlog[index])-1)) return IF(Eventlog[Semi.Mat.No]=previous,0,1)

1.JPG

Regards,
Lydia

Message 2 of 14
2,412 Views
0
Asim
Helper I
Helper I
In response to Anonymous

‎02-06-2018 11:37 PM

Dear Lydia,

Thanks for your reply,

Actually the table is getting filtered by date by machine code and by event, in this case it was showing the records for machine E120N1 with event= setup and with date 15/1/2018.

after applying your suggested columns, I am getting below mentioned result.

 

I also attached one table without applying filter.

 

Regards

Asim

 

 

 Count1.png

 

 

 

Count.png

 

 

Message 3 of 14
2,408 Views
0
Anonymous
Not applicable
In response to Asim

‎02-06-2018 11:40 PM

@Asim,

Please help to share sample data of your original tables and post expected result here.

Regards,
Lydia

Message 4 of 14
2,403 Views
0
Asim
Helper I
Helper I
In response to Anonymous

‎02-07-2018 12:05 AM

@Anonymous

I am new in this site so dont know how to attach the file, however i have copy the table below. 

all i want is to have machine setup counts for each semi material, but if the setup for same material is repeating then the count should be one only.

 

1/16/2018 1:55E120N1setup353543589IIC37TCX6Y
1/16/2018 1:59E120M2halted13543687I2X0R3GESK
1/16/2018 2:00E120M2setup123543684I2X0R3GESKL
1/16/2018 2:12E120M2production673543684I2X0R3GESKL
1/16/2018 2:30E120N1production13543589IIC37TCX6Y
1/16/2018 2:31E120N1halted73543589IIC37TCX6Y
1/16/2018 2:38E120N1production1063543589IIC37TCX6Y
1/16/2018 3:19E120M2halted13543684I2X0R3GESKL
1/16/2018 3:20E120M2setup83543694I2X0R3GESKOS
1/16/2018 3:28E120M2production683543694I2X0R3GESKOS
1/16/2018 3:36E120M1setup603543677I3A0R4HESKKL
1/16/2018 4:24E120N1setup263543589IIC37TCX6R
1/16/2018 4:36E120M1production293543677I3A0R4HESKKL
1/16/2018 4:36E120M2halted13543694I2X0R3GESKOS
1/16/2018 4:37E120M2setup43543688I2X0R3GESK1L
1/16/2018 4:41E120M2production173543688I2X0R3GESK1L
1/16/2018 4:50E120N1halted43543589IIC37TCX6R
1/16/2018 4:54E120N1production1363543589IIC37TCX6R
1/16/2018 4:58E120M2setup33543690I2X2R3GPSK
1/16/2018 5:01E120M2production83543690I2X2R3GPSK
1/16/2018 5:05E120M1setup173543678I3A0R3HESKKL
1/16/2018 5:09E120M2setup43543689I2X0R3GKSKZ
1/16/2018 5:13E120M2production1273543689I2X0R3GKSKZ
1/16/2018 5:22E120M1production463543678I3A0R3HESKKL
1/16/2018 6:08E120M1setup123543822I3A0R3IESKKL
1/16/2018 6:20E120M1production253543822I3A0R3IESKKL
1/16/2018 6:45E120M1setup13543821I3A0R3IPSEK1
1/16/2018 6:46E120M1production353543821I3A0R3IPSEK1
1/16/2018 7:10E120N1setup83543589IIC37TCX6U
1/16/2018 7:18E120N1production423543589IIC37TCX6U
1/16/2018 7:20E120M2setup243543603I2X0R0HEHKZ
1/16/2018 7:21E120M1halted33543821I3A0R3IPSEK1
1/16/2018 7:24E120M1production363543821I3A0R3IPSEK1
1/16/2018 7:44E120M2halted43543603I2X0R0HEHKZ
1/16/2018 7:48E120M2production123543603I2X0R0HEHKZ
1/16/2018 8:00E120M1production233543821I3A0R3IPSEK1
1/16/2018 8:00E120M2production323543603I2X0R0HEHKZ
1/16/2018 8:00E120N1production513543589IIC37TCX6U
1/16/2018 8:23E120M1setup83543747I3D006GBPSEK
1/16/2018 8:31E120M1halted253543747I3D006GBPSEK
1/16/2018 8:32E120M2setup333543633I2X0R0GEHKZ02
1/16/2018 8:51E120N1halted13543589IIC37TCX6U
1/16/2018 8:52E120N1setup1003543809IIC61YCX6K
1/16/2018 8:56E120M1setup333543747I3D006GBPSEK
1/16/2018 9:05E120M2production2373543633I2X0R0GEHKZ02
1/16/2018 9:29E120M1halted33543747I3D006GBPSEK
1/16/2018 9:32E120M1production433543747I3D006GBPSEK
1/16/2018 10:15E120M1setup113543823I3A0R4IESKKL
1/16/2018 10:26E120M1production193543823I3A0R4IESKKL
1/16/2018 10:32E120N1halted53543809IIC61YCX6K
1/16/2018 10:37E120N1production1843543809IIC61YCX6K
1/16/2018 10:45E120M1setup133543824I3A0R2IESKK
1/16/2018 10:58E120M1production233543824I3A0R2IESKK
1/16/2018 11:21E120M1setup113543629I3D007FBESKK
1/16/2018 11:32E120M1production423543629I3D007FBESKK
1/16/2018 12:14E120M1setup363543693I3A0R3GESKKL
1/16/2018 12:50E120M1halted33543693I3A0R3GESKKL
1/16/2018 12:53E120M1production913543693I3A0R3GESKKL
1/16/2018 13:02E120M2setup153543630I2X0R5FPSKE
1/16/2018 13:17E120M2production433543630I2X0R5FPSKE
1/16/2018 13:41E120N1halted283543809IIC61YCX6K
1/16/2018 14:00E120M2halted13543630I2X0R5FPSKE
1/16/2018 14:01E120M2setup223543631I2X2R4FPSK
1/16/2018 14:09E120N1setup153543809IIC61YCX6K
1/16/2018 14:23E120M2production873543631I2X2R4FPSK
1/16/2018 14:24E120M1setup423543760I3A022LESKK
1/16/2018 14:24E120N1production2283543809IIC61YCX6K
1/16/2018 15:06E120M1production193543760I3A022LESKK
1/16/2018 15:25E120M1setup193543628I3D012FBESKK
1/16/2018 15:44E120M1production453543628I3D012FBESKK
1/16/2018 15:50E120M2halted13543631I2X2R4FPSK
1/16/2018 15:51E120M2setup273543740I2C005GBKSK
1/16/2018 16:18E120M2production2223543740I2C005GBKSK
1/16/2018 16:29E120M1setup523543614I3A044MESKK
1/16/2018 17:21E120M1production173543614I3A044MESKK
1/16/2018 17:38E120M1setup373543627I3D019FBESKK
1/16/2018 18:12E120N1halted53543809IIC61YCX6K
1/16/2018 18:15E120M1production643543627I3D019FBESKK
1/16/2018 18:17E120N1setup223543809IIC61YCX6K
1/16/2018 18:39E120N1production813543809IIC61YCX6K
1/16/2018 19:19E120M1setup413543609I5A033MPSEK1
1/16/2018 20:00E120M1setup463543609I5A033MPSEK1
1/16/2018 20:00E120M2production213543740I2C005GBKSK
1/16/2018 20:00E120N1production243543809IIC61YCX6K
1/16/2018 20:21E120M2setup33543741I2C005GBESK
1/16/2018 20:24E120M2production323543741I2C005GBESK
1/16/2018 20:24E120N1halted83543809IIC61YCX6K
1/16/2018 20:32E120N1halted1  
1/16/2018 20:33E120N1setup203543504IIC37T4X6K
1/16/2018 20:46E120M1setup743543835IIC19OCP4L
1/16/2018 20:53E120N1production513543504IIC37T4X6K
1/16/2018 20:56E120M2setup113543739I2C107GBPSK
1/16/2018 21:07E120M2production403543739I2C107GBPSK
1/16/2018 21:44E120N1halted403543504IIC37T4X6K
1/16/2018 21:47E120M2halted13543739I2C107GBPSK
1/16/2018 21:48E120M2setup83543742I2C003GBKSK
1/16/2018 21:56E120M2production643543742I2C003GBKSK
1/16/2018 22:00E120M1production6003543835IIC19OCP4L
1/16/2018 22:24E120N1production23543504IIC37T4X6K
1/16/2018 22:26E120N1halted13543504IIC37T4X6K
1/16/2018 22:27E120N1production1033543504IIC37T4X6K
1/16/2018 23:00E120M2setup133543743I2C003GBKSK
1/16/2018 23:13E120M2production2153543743I2C003GBKSK

 

 

 

regards

asim

Message 5 of 14
2,401 Views
0
Asim
Helper I
Helper I
In response to Asim

‎02-07-2018 12:09 AM

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
Message 6 of 14
2,400 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-07-2018 03:58 AM

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

Regards
Zubair

Please try my custom visuals
Message 7 of 14
2,530 Views
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-19-2018 11:20 PM

@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.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

setup count1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 )

Message 10 of 14
2,365 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-20-2018 12:03 AM

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

Regards
Zubair

Please try my custom visuals
Message 11 of 14
2,027 Views
0
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-20-2018 12:27 AM

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

 

 

 

setup count2.png

 

 

Message 12 of 14
2,020 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Asim

‎02-20-2018 11:15 PM

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 )

Regards
Zubair

Please try my custom visuals
Message 13 of 14
2,484 Views
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-21-2018 09:22 PM

@Zubair_Muhammad,

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

 

thank you very much for your help

 

regards

Asim

Message 14 of 14
2,006 Views
0
Asim
Helper I
Helper I
In response to Zubair_Muhammad

‎02-07-2018 07:55 PM

@Zubair_Muhammad

Good Morning!

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

This solution is perfect!

 

 

Best Regards

Asim 

Message 9 of 14
2,380 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All